How can I make a multiple update in psql?-CodePudding

I have thousand of records I have to update a specific column for all of them in one query. It's an update for broken json literals.

I've tried this:

UPDATE web_scraping.iws_info_web_scraping
SET    iws_location = replace(iws_location, '''', '"')::json ->> 'address'
WHERE   iws_id >= 1
AND iws_location IS DISTINCT FROM replace(iws_location, '''', '"')::json ->> 'address';

I'm trying to update something like this {'address': 'J. Halpert'} into something like this: J. Halpert, but massively.

I'm getting this error I don't know what it means:

ERROR:  invalid input syntax for type json
DETAIL:  Token "DUQUE" is invalid.
CONTEXT:  JSON data, line 1: DUQUE...
SQL state: 22P02

CodePudding user response：

Apparently some of your values are already simple strings, instead of JSON values, but others are strings containing JSON values. As long as there's at least one value that your query can't convert to JSON, the whole query will error. This is messy, but if you can find something that's the same for all of the JSON strings, or for all of the non-JSON strings, you can use a WHERE clause to only work on the JSON rows.

Unfortunately, there is no easy try/catch mechanism in straight PostgreSQL SQL queries, not even for JSON or for casts in general. But from what you said, it sounds like all of your JSON strings start with a { bracket, and I would guess none of the "addresses" start with that symbol. So you could add to your WHERE

AND substring(iws_location from 1 for 1) = '{'