PostgreSQL中更新含NULL值的JSONB列的可行方案

这个问题是PostgreSQL JSONB操作里很常见的小细节——||合并操作符遇到NULL时会直接返回NULL，所以当x.data是NULL时，你的原查询相当于把x.data设置成了NULL，自然看不到预期的更新效果。

修改后的查询 #

最简洁的解决方式是用COALESCE函数把NULL的x.data替换成空的JSONB对象：

UPDATE x 
SET x.data = y.data::jsonb || COALESCE(x.data::jsonb, '{}'::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;

原理说明 #

• COALESCE(a, b)的作用是返回第一个非NULL的值，所以当x.data为NULL时，会用'{}'::jsonb（空JSONB对象）来替代它
• JSONB的||操作符合并空对象和另一个JSON对象时，结果就是那个非空的对象，完美满足你“x.data为NULL时直接用y.data更新”的需求
• 如果x.data本身有值，就会正常执行y.data || x.data的合并逻辑，和你原来的预期完全一致

替代写法（用CASE语句） #

如果你觉得COALESCE不够直观，也可以用CASE语句明确处理两种情况：

UPDATE x 
SET x.data = CASE 
                WHEN x.data IS NULL THEN y.data::jsonb 
                ELSE y.data::jsonb || x.data::jsonb 
             END
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;

这个写法逻辑更直白，效果和上面的COALESCE版本完全一样，选哪种都可以。

内容的提问来源于stack exchange，提问作者Nicholas Tulach