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 — a_horse_with_no_name
My current version of pgAdmin is 4.13 and postgresSql is 9.4.4. — nitesh kumar
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 — a_horse_with_no_name

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

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

Postgres sql jsonb_set() is not working fine

1 Answers