在PostgreSQL中更新JSONB列为NULL值

Question

我试图更新下面的y.data，它是一个JSONB类型的列，目前包含NULL值。当x.data为NULL时，|| 命令似乎无法将y.data与x.data合并在一起。但当x.data 包含一个JSONB值时，就可以正常工作。

以下是一个示例查询。

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;

如何修改此查询以支持更新x.data，以便对于现有值或为NULL的行进行更新？

- Nicholas Tulach

Answer 1

使用 null 进行字符串连接会产生 null。可以使用 coalesce() 函数或条件逻辑来解决此问题：

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

或：

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

请注意，无需将x.data显式转换为jsonb，因为它已经是一个jsonb列了。

- GMB

---

我已经自己解决了你发布的CASE解决方案，但是COALESCE解决方案更短、更清晰，因此是首选答案。谢谢！ - Nicholas Tulach

Answer 2

在SQL中，最好假设一个通用规则，即将NULL添加到任何内容都会使整个内容变为NULL。要处理上述情况，请尝试以下方法：

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]

COALESCE 提供了一个非空的值给 ||。如果你需要不同的值，请修改问题。

- Adrian Klaver