Postgres sql jsonb_set() is not working fine

Question

I am trying to update jsonb value. Please tell me what I did wrong. Here I have a table that has a profile column with jsonb datatype.

UPDATE <TABLE NAME> SET
"profile" = jsonb_set('{"lastName": "dada"}'::jsonb, '{lastName}'::text[] , concat('"', (profile->'lastName'), '"')::jsonb, false)
where email = '[email protected]'

ERROR: function jsonb_set(jsonb, text[], jsonb, boolean) does not exist LINE 2: "profile" = jsonb_set('{"lastName": "dada"}'::jsonb, '{lastN... ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. SQL state: 42883 Character: 44

What is your Postgres version? select version(); will tell you — user330315
– user330315, Commented Dec 13, 2019 at 12:36
My current version of pgAdmin is 4.13 and postgresSql is 9.4.4. — nitesh kumar
– nitesh kumar, Commented Dec 23, 2019 at 6:30
jsonb_set was introduced in Postgres 9.5, it's not available in 9.4 - but 9.4 will be unsupported very soon, so you should plan an upgrade anyway — user330315
– user330315, Commented Dec 23, 2019 at 6:50

Bjarni Ragnarsson · Accepted Answer · 2019-12-13 12:52:19Z

2

Here is a correction:

UPDATE <TABLE NAME> SET
profile = jsonb_set('{"lastName": "dada"}'::jsonb, '{lastName}'::text[] , 
                      (profile->'lastName')::jsonb, false)
where email = '[email protected]'

The error was here: concat('"', (profile->'lastName'), '"')::jsonb

Mind you though, this just sets the lastname field back to the sam lastname value :-)

Best regards,
Bjarni

answered Dec 13, 2019 at 12:52

Bjarni Ragnarsson

1,7811 gold badge8 silver badges8 bronze badges

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

1 Comment

nitesh kumar Over a year ago

function jsonb_set(jsonb, text[], jsonb, boolean) does not exist. I am still getting this error.

Collectives™ on Stack Overflow

Postgres sql jsonb_set() is not working fine

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related