带唯一约束的PostgreSQL表更新：如何避免约束冲突

有一张带唯一约束的临时表，建表及插入数据的SQL如下：

CREATE temp TABLE tmp (
    c1 int,
    c2 text,
    c3 int,
    UNIQUE (c2, c3)
);

insert into tmp (c1, c2, c3)
values  (1, 'a', 2),
        (2, 'a', 1)

请问是否可以在不修改表tmp结构、且不触发唯一约束冲突的情况下执行以下UPDATE语句？

update tmp
set c3 = c1
where 1=1

执行该语句时会报错：

ERROR: duplicate key value violates unique constraint "tmp_c2_c3_key". Detail: Key (c2, c3)=(a, 1) already exists.

可以做到。问题出在PostgreSQL默认会逐行执行更新并即时检查唯一约束：当更新第一行(1, 'a', 2)为(1, 'a', 1)时，会发现第二行已经存在(c2,c3)=(a,1)，直接触发冲突。

解决方法是在事务中延迟唯一约束的检查，直到事务提交时再验证最终状态：

BEGIN;
-- 延迟指定唯一约束的检查时机
SET CONSTRAINTS tmp_c2_c3_key DEFERRED;
UPDATE tmp SET c3 = c1 WHERE 1=1;
COMMIT;

事务提交时，两行数据的最终状态为(1, 'a', 1)和(2, 'a', 2)，(c2,c3)组合没有重复，因此不会触发唯一约束冲突。这种方式不需要修改表结构，完全满足要求。

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