PostgreSQL联合唯一约束下交换行字段值触发约束冲突问题

PostgreSQL对非可延迟约束默认采用逐行即时校验机制：每更新完一行就立刻检查该行是否违反约束规则，不会等整个UPDATE语句所有行更新完成后再做全量校验。
执行值交换语句时，当第一行的b值从0改为1，数据库会立刻检测到同a值下已存在b=1的未更新行，直接抛出联合唯一约束冲突错误，不会继续执行后续行的更新。

注意：table是SQL保留关键字，不建议直接作为表名使用，以下示例统一用your_table指代业务表名，请替换为实际表名使用。

方案1：调整约束为可延迟模式（推荐） #

该方案对业务更新逻辑侵入最小，核心是将唯一约束的校验时机从「逐行更新后」调整为「事务提交时」，等所有行更新完成后再统一校验唯一性，不会破坏数据一致性。

• 调整现有联合唯一约束属性

-- 先删除原有的非可延迟联合唯一约束，请替换为实际的约束名
ALTER TABLE your_table DROP CONSTRAINT uk_your_table_a_b;

-- 新增可延迟联合唯一约束，默认事务提交时触发校验
ALTER TABLE your_table 
ADD CONSTRAINT uk_your_table_a_b 
UNIQUE (a, b) 
DEFERRABLE INITIALLY DEFERRED;

• 约束调整完成后，直接执行原有更新逻辑即可，无需额外修改

UPDATE your_table 
SET b = CASE WHEN b=0 THEN 1 ELSE 0 END 
WHERE id <= 4;

方案2：临时中转值规避冲突（无需修改表结构） #

如果没有修改表约束的权限，可以通过两次更新，用无业务含义的临时值做中转，避开中间状态的冲突：

-- 第一步：将待更新行的b值替换为和现有有效值不冲突的临时值，保留原值映射关系
-- 请选择b列业务场景中不会用到的数值作为临时值，示例用2、3对应原值0、1
UPDATE your_table
SET b = CASE WHEN b = 0 THEN 2 ELSE 3 END
WHERE id <= 4;

-- 第二步：将临时值转换为最终交换后的目标值
UPDATE your_table
SET b = CASE WHEN b = 2 THEN 1 ELSE 0 END
WHERE b IN (2, 3);

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