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?
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.
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.