PostgreSQL中ON CONFLICT ... DO UPDATE SET语句因重复行执行失败问题求助

这个问题我之前碰到过，根源很明确：你一次性插入的两行数据，它们的(id, event_timestamp)组合完全一致——而这正是你指定的冲突判断键。PostgreSQL在处理第一条记录时，已经对目标行执行了UPDATE操作；当处理第二条相同的记录时，又要再次修改同一行，这就触发了ERROR: ON CONFLICT DO UPDATE command cannot affect row a second time的限制。

下面给你几个适配不同场景的解决方案：

方案1：先对插入数据去重（最常用） #

因为你的数据来自外部系统，天然可能存在重复，所以在插入前先过滤掉同一批次内的重复行，确保每个(id, event_timestamp)组合只出现一次。可以用子查询+分组的方式实现：

INSERT INTO loger(state, id, event_timestamp, other_event_timestamp)
SELECT state, id, event_timestamp, other_event_timestamp
FROM (
  VALUES 
    (1, 12, '2020-01-01T19:00:00.000Z', '2020-01-01T19:00:00.000Z'),
    (1, 12, '2020-01-01T19:00:00.000Z', '2020-01-01T19:00:00.000Z')
) AS temp(state, id, event_timestamp, other_event_timestamp)
-- 按所有字段分组，过滤掉重复行
GROUP BY state, id, event_timestamp, other_event_timestamp
ON CONFLICT(id, event_timestamp) DO UPDATE SET state = excluded.state;

如果你的重复行只有冲突键相同，其他字段可能有差异，还可以用DISTINCT ON来指定保留哪一行（比如保留最后一行）：

INSERT INTO loger(state, id, event_timestamp, other_event_timestamp)
SELECT DISTINCT ON(id, event_timestamp) state, id, event_timestamp, other_event_timestamp
FROM (
  VALUES 
    (1, 12, '2020-01-01T19:00:00.000Z', '2020-01-01T19:00:00.000Z'),
    (2, 12, '2020-01-01T19:00:00.000Z', '2020-01-01T19:05:00.000Z')
) AS temp(state, id, event_timestamp, other_event_timestamp)
-- 可以指定排序规则，决定保留哪一行
ORDER BY id, event_timestamp, other_event_timestamp DESC
ON CONFLICT(id, event_timestamp) DO UPDATE SET state = excluded.state;

方案2：调整唯一约束（如果业务允许） #

如果业务上其实允许同一(id, event_timestamp)存在多行数据，那你当前的唯一约束(id, event_timestamp)就不符合需求了。可以考虑把other_event_timestamp也加入唯一约束，或者直接删除这个唯一约束（根据实际业务逻辑调整）。不过这种方案需要先评估业务影响，因为会改变表的约束规则。

方案3：改用批量插入+单独处理冲突（适合复杂场景） #

如果必须保留所有原始行的处理记录，可以拆分操作：先插入所有不冲突的行，再单独处理冲突的行。不过这种方案相对繁琐，一般只在特殊业务需求下使用。

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