PostgreSQL 14多约束场景下Upsert实现方案咨询

针对你的场景（表存在单列a唯一约束、b,c,d,e联合唯一约束，需实现违反任意一个约束时执行Update而非报错），在PostgreSQL 14中可以通过以下两种可行方案实现，无需依赖PostgreSQL 15的MERGE语法：

方案一：PL/pgSQL逐行捕获异常处理（适合小数据量） #

利用PostgreSQL的异常捕获机制，逐行尝试插入，若因a约束冲突则直接更新；若因b,c,d,e约束冲突则捕获异常并更新对应行：

DO $$
DECLARE
    temp_rec RECORD;
BEGIN
    FOR temp_rec IN SELECT a, b, c, d, e, f, g FROM my_temp_table LOOP
        BEGIN
            -- 优先处理a列唯一约束冲突
            INSERT INTO my_table (a, b, c, d, e, f, g)
            VALUES (temp_rec.a, temp_rec.b, temp_rec.c, temp_rec.d, temp_rec.e, temp_rec.f, temp_rec.g)
            ON CONFLICT (a) DO UPDATE SET
                b = EXCLUDED.b,
                c = EXCLUDED.c,
                d = EXCLUDED.d,
                e = EXCLUDED.e,
                f = EXCLUDED.f,
                g = EXCLUDED.g;
        EXCEPTION
            WHEN unique_violation THEN
                -- 捕获到b,c,d,e联合约束冲突，执行对应更新
                UPDATE my_table
                SET a = temp_rec.a,
                    f = temp_rec.f,
                    g = temp_rec.g
                WHERE b = temp_rec.b 
                  AND c = temp_rec.c 
                  AND d = temp_rec.d 
                  AND e = temp_rec.e;
        END;
    END LOOP;
END $$;

注意：若存在同时违反两个约束的行（如某行的a已存在，且b,c,d,e组合也已存在），此方案会优先按a约束处理，若仍触发异常则进入EXCEPTION块处理b,c,d,e约束，需根据你的业务逻辑调整优先级。

方案二：WITH子句批量处理（适合大数据量） #

通过CTE（公共表表达式）分三步批量处理，避免逐行循环的性能开销：

BEGIN;
WITH 
-- 第一步：更新所有与临时表a列冲突的记录
update_conflict_a AS (
    UPDATE my_table t
    SET b = temp.b, c = temp.c, d = temp.d, e = temp.e, f = temp.f, g = temp.g
    FROM my_temp_table temp
    WHERE t.a = temp.a
    RETURNING temp.a, temp.b, temp.c, temp.d, temp.e
),
-- 第二步：更新所有与临时表b,c,d,e组合冲突的记录（排除已被第一步处理的行）
update_conflict_bcde AS (
    UPDATE my_table t
    SET a = temp.a, f = temp.f, g = temp.g
    FROM my_temp_table temp
    WHERE t.b = temp.b 
      AND t.c = temp.c 
      AND t.d = temp.d 
      AND t.e = temp.e
    AND NOT EXISTS (
        SELECT 1 FROM update_conflict_a ua
        WHERE ua.a = temp.a 
          AND ua.b = temp.b 
          AND ua.c = temp.c 
          AND ua.d = temp.d 
          AND ua.e = temp.e
    )
    RETURNING temp.a, temp.b, temp.c, temp.d, temp.e
),
-- 第三步：插入无任何约束冲突的新记录
insert_new_rows AS (
    INSERT INTO my_table (a, b, c, d, e, f, g)
    SELECT temp.a, temp.b, temp.c, temp.d, temp.e, temp.f, temp.g
    FROM my_temp_table temp
    WHERE NOT EXISTS (
        SELECT 1 FROM update_conflict_a ua
        WHERE ua.a = temp.a 
          AND ua.b = temp.b 
          AND ua.c = temp.c 
          AND ua.d = temp.d 
          AND ua.e = temp.e
    )
    AND NOT EXISTS (
        SELECT 1 FROM update_conflict_bcde ub
        WHERE ub.a = temp.a 
          AND ub.b = temp.b 
          AND ub.c = temp.c 
          AND ub.d = temp.d 
          AND ub.e = temp.e
    )
    RETURNING *
)
-- 输出处理统计结果
SELECT
    (SELECT COUNT(*) FROM update_conflict_a) AS updated_via_a_constraint,
    (SELECT COUNT(*) FROM update_conflict_bcde) AS updated_via_bcde_constraint,
    (SELECT COUNT(*) FROM insert_new_rows) AS inserted_new_records;
COMMIT;

优势：批量处理效率更高，适合大数据量场景；通过事务保证原子性，避免部分更新/插入导致的数据不一致。

关键注意事项 #

1. 两种方案都需在事务中执行，确保操作的原子性
2. 若存在同时违反两个约束的行，需明确业务逻辑优先级（优先按a还是b,c,d,e处理），并调整代码中的处理顺序
3. 并发场景下需注意锁竞争，可根据实际情况调整事务隔离级别（如REPEATABLE READ）

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