0

I have a lot of character varying records in this format: {'address': 'New Mexico'}.
I would like to update all those columns to have it like this: New Mexico.

I've been investigating how to do it, and it could be with regexp, but I don't know how to make for all columns in a table, and I never used regex in PostgreSQL before.

I have an idea that is something like this:

SET location = regexp_replace(field, 'match pattern', 'replace string', 'g')
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
marshel
  • 13
  • 2
  • Postgres has built in tools for manipulating json. Have you tried something like `location::json->>'address'` ? – Nick ODell Nov 26 '22 at 02:13
  • I'm trying this: UPDATE web_scraping.iws_informacion_web_scraping SET iws_localizacion = iws_localizacion::json -> 'address' WHERE iws_id = 3678; But I'm getting this: ERROR: invalid input syntax for type json DETAIL: Token "'" is invalid. CONTEXT: JSON data, line 1: {'... SQL state: 22P02 – marshel Nov 26 '22 at 03:23
  • This is the reason the column should have been defined as `jsonb` (or `json`) from the start. Then you wouldn't need to fix the data now. –  Nov 26 '22 at 06:50

1 Answers1

0

Valid JSON literals require double-quotes where your sample displays single quotes. Maybe you can fix that upstream?

To repair (assuming there are no other, unrelated single-quotes involved):

UPDATE web_scraping.iws_informacion_web_scraping
SET    iws_localizacion = replace(iws_localizacion, '''', '"')::json ->> 'address'
WHERE  iws_id = 3678
AND    iws_localizacion IS DISTINCT FROM replace(iws_localizacion, '''', '"')::json ->> 'address';

The 2nd WHERE clause prevents updates to rows that wouldn't change. See:

Optional if such cases can be excluded.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • Great! It works smoothly, just a question, why in the replace clause you use four single quotation marks instead of three, I understand we want to change ' for '', but when I remove it I get an error, I suppose it's because postgresql detect it like a quotation mark without its respective closing quotation mark. – marshel Nov 26 '22 at 11:59
  • @marshelv: About single quotes in quoted strings, see: https://stackoverflow.com/a/12320729/939860 – Erwin Brandstetter Nov 26 '22 at 21:33