PostgreSQL中JSONB列的插入与更新实现方法咨询

你需要修改ON CONFLICT部分的逻辑，利用PostgreSQL的jsonb合并特性实现冲突时的字段更新，完整SQL语句如下：

INSERT INTO perm_table(ticker, data)
SELECT ticker, data FROM temp_table
ON CONFLICT (ticker) DO UPDATE 
SET data = perm_table.data || EXCLUDED.data;

逻辑说明： #

• ON CONFLICT (ticker)：指定主键ticker作为冲突判断字段，当插入的ticker与表中现有主键重复时触发后续更新逻辑。
• DO UPDATE SET data = perm_table.data || EXCLUDED.data：
  • EXCLUDED是PostgreSQL内置的特殊行记录，代表本次插入操作中冲突的待插入数据（即临时表temp_table里的对应记录）。
  • ||是jsonb类型的合并操作符，会将原表perm_table中已有的data对象，与待插入的EXCLUDED.data对象合并。针对你场景里的嵌套sales对象，新的季度日期键值对会被追加进去，不会覆盖原有数据（如果存在重复键则新值会覆盖旧值，你的场景是新增季度数据，日期无重复，完全适配）。

执行语句后，perm_table中XYZ的data字段会更新为：

{"sales":{"2022-06-30":1500,"2022-03-31":1200,"2022-09-30":1800}}

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