PostgreSQL多唯一约束下MERGE INTO报错及优化需求问询

问题1：避免MERGE报错的可行SQL语句 #

MERGE报错的核心是单个目标行被多个源行匹配（你的场景中目标行temp3/temp4同时被源行1（temp1/temp4，col2匹配）和源行3（temp3/temp6，col1匹配）命中）。要解决这个问题，必须先确保源数据到目标数据是一对一的映射关系，再执行MERGE或分阶段操作。

方案1：分阶段处理冲突（优先主键，再处理唯一约束） #

利用PostgreSQL的INSERT ... ON CONFLICT分别处理两种冲突，避免交叉匹配：

1. 先处理主键（col1）冲突的更新/插入

-- 处理col1匹配的情况：更新非主键列，插入无冲突的行
INSERT INTO some_table (col1, col2, other_cols)
SELECT col1, col2, other_cols FROM source_data
ON CONFLICT (col1) DO UPDATE
SET col2 = EXCLUDED.col2, other_cols = EXCLUDED.other_cols;

2. 再处理col2唯一约束的冲突（仅针对col1未匹配的源行）

-- 先筛选出col1不在目标表中的源行，再处理col2冲突
WITH unmatched_source AS (
    SELECT col1, col2, other_cols FROM source_data
    WHERE col1 NOT IN (SELECT col1 FROM some_table)
)
UPDATE some_table t
SET col1 = u.col1, other_cols = u.other_cols
FROM unmatched_source u
WHERE t.col2 = u.col2;

-- 插入剩余无任何冲突的行
INSERT INTO some_table (col1, col2, other_cols)
SELECT col1, col2, other_cols FROM unmatched_source u
WHERE u.col2 NOT IN (SELECT col2 FROM some_table);

方案2：预处理源数据，确保MERGE时一对一匹配 #

如果一定要用MERGE，先对源数据做优先级过滤，确保每个目标行最多被一个源行匹配（比如规定主键匹配优先于col2匹配）：

MERGE INTO some_table AS target
USING (
    SELECT DISTINCT ON (coalesce(t.col1, s.col2)) -- 按匹配优先级去重
        s.col1, s.col2, s.other_cols
    FROM source_data s
    LEFT JOIN some_table t
        ON s.col1 = t.col1 OR s.col2 = t.col2
    ORDER BY coalesce(t.col1, s.col2), s.col1 -- 主键匹配的行优先保留
) AS source(col1, col2, other_cols)
ON (target.col1 = source.col1 OR target.col2 = source.col2)
WHEN MATCHED AND target.col1 = source.col1 THEN
    UPDATE SET col2 = source.col2, other_cols = source.other_cols
WHEN MATCHED AND target.col2 = source.col2 THEN
    UPDATE SET col1 = source.col1, other_cols = source.other_cols
WHEN NOT MATCHED THEN
    INSERT (col1, col2, other_cols)
    VALUES (source.col1, source.col2, source.other_cols);

问题2：高效性保障（针对8亿数据量） #

• 依赖索引：确保col1（主键默认有索引）和col2（唯一约束默认有索引）的索引生效，所有过滤、匹配操作都走索引扫描，避免全表扫描。
• 批量分批处理：不要一次性处理所有数据，将源数据分成若干批次（比如每批次10万行），减少锁竞争和内存占用。
• 缓存中间结果：用CTE或临时表缓存源数据和过滤后的结果，避免重复查询8亿行的大表。
• 临时关闭约束：如果能保证源数据合法，可临时删除col2的唯一约束，完成操作后重建索引（重建耗时但比批量冲突检查高效）。

问题3：双唯一约束的其他边缘场景 #

• 源数据内部冲突：源数据中存在多行col1重复，或多行col2重复，插入时直接触发主键/唯一约束报错。
• 交叉冲突连锁反应：比如目标表有(a,b)，源数据有(b,c)和(c,a)，更新后可能触发新的col2冲突。
• 更新导致新冲突：更新某行的col2值为另一行的col2值，触发唯一约束报错。
• 事务死锁：多个事务同时修改涉及col1和col2交叉匹配的行，比如事务1更新(a,b)为(a,c)，事务2更新(d,c)为(d,b)，可能导致死锁。
• 主键与唯一约束值互换：目标表有(x,y)，源数据有(y,x)，插入时会同时触发col1主键冲突和col2唯一约束冲突。
• 批量操作性能雪崩：当批量数据中冲突比例极高时，大量冲突检查会导致性能急剧下降，甚至拖垮数据库。

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