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?

Answer 1

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.