CODEWITHSUNDEEP
postgresqljsonb
Catfish
Catfish

Reputation: 19284

How to set a JSONB value to null

I'm trying to update a bunch of jsonb values to null. Here's an example of what i'm trying to do and i'm getting the error below.

How can I set first-name to null in this case?

UPDATE users
SET
    fields = fields || '{"first-name": NULL}'
WHERE user_id = 1;

ERROR:  invalid input syntax for type json
LINE 3:  fields = fields || '{"first-name": NULL}'
                                    ^
DETAIL:  Token "NULL" is invalid.
CONTEXT:  JSON data, line 1: {"first-name": NULL...

Upvotes: 7

Views: 12585

Answers (2)

jjanes
jjanes

Reputation: 44137

If you want the null value inside the JSONB, then it must be a JSON null value, not an SQL NULL value. JSON null must be spelled in lower case.

Upvotes: 4

Laurenz Albe
Laurenz Albe

Reputation: 246328

Use jsonb_set:

UPDATE users
SET
    fields = jsonb_set(fields, '{first-name}', 'null')
WHERE user_id = 1;

Upvotes: 7

Related Questions