Postgresql update json error with `invalid input syntax for type json, Token is invalid.`

Question

This is my SQL :

UPDATE "CET_User"
   SET "Property" = jsonb_set("Property",
                              '{"Tid"}',
                              '9323fce7-416e-40be-802f-ee173f246907')
 WHERE ("Property" - >> 'Tid') = '33bc69e8-0715-4b86-8611-67070c5c744d';

When i try to run it , it get a error :

ERROR:  invalid input syntax for type json
LINE 13: ...SET  "Property" = jsonb_set("Property",'{"Tid"}', '9323fce7-...
                                                              ^
DETAIL:  Token "9323fce7" is invalid.
CONTEXT:  JSON data, line 1: 9323fce7...

ref :

https://www.postgresql.org/docs/15/functions-json.html

I think i use the right syntax, is there any fault observed?

lyj · Accepted Answer · 2023-01-15 06:44:04Z

1

Think abuout this value, '9323fce7-416e-40be-802f-ee173f246907', the jsonb_set function need a jsonb value so it will try to translate to it.
Like SELECT '9323fce7-416e-40be-802f-ee173f246907'::jsonb, you need specify it is a string, SELECT '"9323fce7-416e-40be-802f-ee173f246907"'::jsonb

So, the answer is UPDATE "CET_User" SET "Property" = jsonb_set("Property",'{"Tid"}', '"9323fce7-416e-40be-802f-ee173f246907"') WHERE ("Property"->>'Tid') = '33bc69e8-0715-4b86-8611-67070c5c744d';

answered Jan 15, 2023 at 6:44

lyj

5605 silver badges7 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

AKumar · Accepted Answer · 2024-11-04 02:15:59Z

-1

The previous answer is correct. i was using json_set for text column values instead of hardcoded values so i added the following for column '"'||b.columnname||'"' as newcolumn and then using the above column in the jsonb_set function as below

jsonb_set(cell_data,'{binding,group,id}',newcolumn::jsonb) it worked for me.

So just wrapping the values with double quotes can do the trick.

answered Nov 4, 2024 at 2:15

AKumar

193 bronze badges

Collectives™ on Stack Overflow

Postgresql update json error with `invalid input syntax for type json, Token is invalid.`

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related