Update JSONB column with NULL value in PostgreSQL

Question

I'm trying to update the following y.data, which is a JSONB type column that currently contains a NULL value. The || command does not seem to work merging together y.data with x.data when x.data is NULL. It works fine when x.data contains a JSONB value.

Here is an example query.

UPDATE x
SET x.data = y.data::jsonb || x.data::jsonb
FROM (VALUES ('2018-05-24', 'Nicholas', '{"test": "abc"}')) AS y (post_date, name, data)
WHERE x.post_date::date = y.post_date::date AND x.name = y.name;

What would be the best way to modify this query to support updating x.data for rows that both have existing values or are NULL?

GMB · Accepted Answer · 2020-06-29 14:33:36Z

5

Concatenating anything with null produces null. You can use coalesce() or conditional logic to work around this:

SET x.data = COALESCE(y.data::jsonb || x.data, y.data::jsonb)

Or:

SET x.data = CASE WHEN x.data IS NULL 
    THEN y.data::jsonb
    ELSE y.data::jsonb || x.data
END

Note that there is no need to explictly cast x.data to jsonb, since it's a jsonb column already.

answered Jun 29, 2020 at 14:33

GMB

224k25 gold badges103 silver badges151 bronze badges

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

1 Comment

Nicholas Tulach Over a year ago

I worked out the CASE solution you posted for myself, but the COALESCE solution is shorter, clearer, and therefore the preferred answer. Thanks!

Adrian Klaver · Accepted Answer · 2020-06-29 14:39:56Z

0

In SQL it is best to assume as a general rule that adding NULL to something makes the whole thing NULL. To deal with the above try something like:

test(5432)=# SELECT '[1, 2, "foo", null]'::jsonb || null;
 ?column? 
----------
 NULL

SELECT '[1, 2, "foo", null]'::jsonb || coalesce(NULL, '[]'::jsonb);
      ?column?       
---------------------
 [1, 2, "foo", null]

The COALESCE supplies something to the || that is NOT NULL. If you want something different then modify question to indicate.

answered Jun 29, 2020 at 14:39

Adrian Klaver

20.3k3 gold badges24 silver badges40 bronze badges

Collectives™ on Stack Overflow

Update JSONB column with NULL value in PostgreSQL

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related