PostgreSQL 批量 upsert 报 cannot affect row a second time

单条记录可以插入或更新，合成一批后却失败，日志出现：

ERROR: ON CONFLICT DO UPDATE command cannot affect row a second time
HINT: Ensure that no rows proposed for insertion within the same command have duplicate constrained values.

需要检查同一条 SQL 的输入行。ON CONFLICT DO UPDATE 可以处理输入与目标表已有记录的冲突，但不允许一条命令反复影响同一条目标记录。两条输入落到同一个冲突键上，就可能触发这个错误；目标表原本为空也不能排除它。

本文讨论 PostgreSQL 的批量 INSERT ... ON CONFLICT DO UPDATE，适用于多行 VALUES、INSERT ... SELECT，以及驱动最终合并成一条 SQL 的批处理。其他数据库的 upsert 行为不能直接套用。

按冲突键检查这一批数据

先找实际 SQL 中的 ON CONFLICT (...) 或指定的约束名，确认冲突依据。目标表有自增主键，不表示 upsert 就按这个主键匹配；代码可能指定的是业务编码或复合唯一键。

假设目标表 item_state 以非空文本列 item_key 为主键，要把临时导入表 incoming_state 中的 state、event_time 写入。下面的诊断查询只读取输入：

SELECT item_key, count(*) AS occurrences
FROM incoming_state
GROUP BY item_key
HAVING count(*) > 1;

有结果，说明输入中一个键出现了多次。若实际冲突键是 (tenant_id, item_key)，就必须按这两列一起分组；只按 item_key 去重会误删不同租户的数据。表名、列名均为示例，查询范围应限定为这次失败的批次。

对于 INSERT ... SELECT，要检查的是执行完连接、过滤和字段转换后的 SELECT 结果。源表本身没有重复，不代表一对多连接之后仍然唯一。应用层批量写入则应记录脱敏后的冲突键和批次边界，不能只看日志里打印的一行 SQL 模板。

如果输入看起来唯一，还要核对唯一索引是否使用表达式、比较规则，以及 BEFORE INSERT 触发器是否改写键值。例如原始文本不同，统一小写后可能变成同一个键。这时按原始值分组不足以排除冲突，应按实际索引和转换规则继续缩小范围。

去重前决定保留哪条记录

两条输入的键相同，其他字段可能不同。直接删除“多出来的一条”会隐含业务选择：保留最新状态、保留最早事件，还是把多条数量累加，结果并不一样。

如果业务要求每个键只保留这批中时间最新的一行，可以在写入前选出唯一候选。以下示例假定：

incoming_state 只包含本批数据，item_key 非空。
event_time 表示业务事件时间；空时间排在有时间的记录之后。
input_seq 非空且在批内唯一；时间相同时，较大的序号优先。
item_state 的主键为 item_key，并有 state、event_time 两列。

WITH chosen AS (
    SELECT DISTINCT ON (item_key)
           item_key, state, event_time
    FROM incoming_state
    ORDER BY item_key, event_time DESC NULLS LAST, input_seq DESC
)
INSERT INTO item_state (item_key, state, event_time)
SELECT item_key, state, event_time
FROM chosen
ON CONFLICT (item_key) DO UPDATE
SET state = EXCLUDED.state,
    event_time = EXCLUDED.event_time;

DISTINCT ON (item_key) 让每个键只向 INSERT 提交一行。排序的最左项与去重键一致，后面的时间和序号决定选中谁。没有完整排序时，不能承诺一定保留“最后一条”。不要随意用最大 ID 代替最新业务时间。

这个例子只选择本批中的最新记录，然后覆盖目标值。它没有阻止一批较旧的数据覆盖目标表中更晚的状态。如果业务要求跨批次也禁止旧值覆盖，还需要另行设计目标时间比较、空值和同时间冲突策略，不能把批内去重当作完整的事件顺序保证。

普通 SELECT DISTINCT item_key, state, event_time 按全部输出列去重。同一个键只要状态或时间不同，两行就仍会保留，错误可能照旧出现。需要汇总数量时应按冲突键聚合，并同时明确其他字段如何选取，不能随意丢弃它们。

批处理层也要保持相同的键语义

如果应用已经把同一事件展开成多条投递记录，而存储表只记录“事件与订阅”这一组合，就应在组装待写数据时按完整复合键合并。负责执行批量写入的方法也可以再次校验，避免其他调用路径传入重复键。

去重时应保留一整条记录，或按同一组保留位置同步筛选所有字段。若程序用多个并行数组传参数，只对键数组去重，会让状态、时间等字段与键错位。

有些驱动会把多次插入合并成一条多行 INSERT。是否启用这种行为，需要核对当前驱动配置和最终 SQL。把批次拆成单行可能改变错误表现，却也可能把同一个键更新多次，最终值依赖执行顺序；它不等于已经解决了重复输入的含义。

同样，不应仅为消除报错改成 DO NOTHING：它会跳过冲突记录，原本需要更新的状态也可能不再更新。只有业务本来就要求保留已有记录时，这才是合适的语义。

确认写入内容，再恢复重试

先用与写入完全相同的筛选和排序条件预览候选结果，确认每个冲突键只有一条、保留的状态与时间符合规则。修复后的检查至少覆盖同键同内容、同键不同时间、同时间不同序号，以及复合键中另一列不同的情况。

失败的那条 INSERT 不会留下部分成功写入，但一个应用批次可能对应多条 SQL 或多个事务。重试前应核对实际提交边界和已落库的键，不要把整批应用数据都当作未写入。若外层显式事务已因错误进入失败状态，先按事务设计回滚，再重新提交修正后的输入。

保留失败批次的原始输入和既有去重规则，便于查明被舍弃的记录。修复上线后，对比输入唯一键数量、实际插入或更新结果和目标值；不要只看异常是否消失。若新规则选错了记录，暂停后续导入，依据保留的输入与写入前备份校正受影响的数据。恢复旧代码不会自动恢复被覆盖的字段。