PostgreSQL中jsonb字段用jsonb_set更新null值行无效果如何解决

根因分析 #

jsonb_set 函数的第一个入参（待操作的jsonb对象）为null时，函数返回结果直接为null，因此更新后目标字段仍保持原值，不会产生预期的写入效果，这就是字段为null的行执行命令不生效的原因。

通用解决方案 #

使用coalesce函数对空值做兼容处理，当字段为null时先初始化为空jsonb对象，再执行更新操作，该写法对字段已存在数据、字段为null的场景都生效：

UPDATE "MddPublisher" SET "CountriesAndGamesAppStore" = jsonb_set(
  coalesce("CountriesAndGamesAppStore", '{}'::jsonb),
  '{"AX"}',
  '["test"]'
);

可选优化：仅更新空值行 #

如果需求是仅给字段为null的行初始化AX对应的配置，可以加WHERE条件缩小更新范围，避免全表扫描提升执行效率：

UPDATE "MddPublisher" 
SET "CountriesAndGamesAppStore" = '{"AX": ["test"]}'::jsonb
WHERE "CountriesAndGamesAppStore" IS NULL;

注意事项 #

'{}'::jsonb的显式类型转换不可省略，否则PostgreSQL会将空对象识别为普通字符串，触发类型不匹配报错。

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