Postgres如何将分组多行合并为单行插入目标表

解决方案 #

你遇到的报错本质是同个Id对应多行原始数据，插入时会先后触发同一Id的多次冲突更新，PostgreSQL不允许同事务内多次修改同一行。只要先把原始长表按Id聚合成宽表结构，再执行插入操作即可解决问题。

1. 核心逻辑：长表转宽表

使用PostgreSQL的FILTER聚合语法可以非常简洁的完成行列转换，每个Id仅返回一行结果：

SELECT
  Id,
  MAX(value) FILTER (WHERE category = 'a') AS category_a_value,
  MAX(value) FILTER (WHERE category = 'b') AS category_b_value
FROM 替换为你的原始数据查询语句
GROUP BY Id

如果使用的是10以下不支持FILTER的旧版本PostgreSQL，可以用CASE WHEN替代：

SELECT
  Id,
  MAX(CASE WHEN category = 'a' THEN value END) AS category_a_value,
  MAX(CASE WHEN category = 'b' THEN value END) AS category_b_value
FROM 替换为你的原始数据查询语句
GROUP BY Id

2. 插入目标表（支持冲突更新）

把上面的聚合查询结果直接作为插入的数据源即可，因为每个Id只有一行数据，不会再触发同个行多次修改的报错：

INSERT INTO 你的目标表名 (Id, category_a_value, category_b_value)
SELECT
  Id,
  MAX(value) FILTER (WHERE category = 'a') AS category_a_value,
  MAX(value) FILTER (WHERE category = 'b') AS category_b_value
FROM 替换为你的原始数据查询语句
GROUP BY Id
-- 如果需要处理Id重复的冲突场景，保留下面的ON CONFLICT逻辑，不需要可以删掉
ON CONFLICT (Id) DO UPDATE SET
  category_a_value = EXCLUDED.category_a_value,
  category_b_value = EXCLUDED.category_b_value;

如果后续新增了其他category类型，只需要在SELECT部分新增对应的FILTER聚合列即可，整体逻辑不需要调整。

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