PostgreSQL中如何通过Upsert交换两行的ColumnName值？

表结构与初始数据 #

创建表的SQL：

CREATE TABLE IF NOT EXISTS MyTable
(
    ColumnID1 integer NOT NULL,
    ColumnID2 integer NOT NULL,
    ColumnName character varying(1000),
    CONSTRAINT "MyTable_pkey" PRIMARY KEY (ColumnID1, ColumnID2),
    CONSTRAINT "MyTable-ColumnID1-ColumnName-Unique-Key" UNIQUE (ColumnID1, ColumnName)    
)

插入初始数据：

insert into MyTable
values 
(1, 1, 'a'), (1, 2, 'b')

问题场景 #

常规更新操作可正常执行：

-- 更新两行数据（执行成功）
insert into MyTable (ColumnID1, ColumnID2, ColumnName)
values (1, 1, 'c'), (1, 2, 'd')
on conflict (ColumnID1, ColumnID2)
do update
set
    ColumnName = EXCLUDED.ColumnName

但尝试交换两行的ColumnName值时，触发唯一约束错误：

insert into MyTable (ColumnID1, ColumnID2, ColumnName)
values (1, 1, 'd'), (1, 2, 'c')
on conflict (ColumnID1, ColumnID2)
do update
set
    ColumnName = EXCLUDED.ColumnName

错误信息：

ERROR:  Key (columnid1, columnname)=(1, d) already exists.duplicate key value violates unique constraint "MyTable-ColumnID1-ColumnName-Unique-Key" 

ERROR:  duplicate key value violates unique constraint "MyTable-ColumnID1-ColumnName-Unique-Key"
SQL state: 23505
Detail: Key (columnid1, columnname)=(1, d) already exists.

原因分析 #

PostgreSQL默认的唯一约束为立即检查（NOT DEFERRABLE），执行Upsert时会逐行处理：

1. 先尝试更新(1,1)的ColumnName为'd'，此时原表中(1,2)的ColumnName仍为'd'，直接触发(ColumnID1, ColumnName)的唯一约束冲突。
2. 后续行的更新无法执行。

解决方案 #

方法1：修改唯一约束为可延迟检查 #

将唯一约束改为延迟检查，让PostgreSQL等到事务提交时再验证约束，确保两行交换值的操作在事务内完成后，整体不违反约束。

首先删除原约束并重新创建：

-- 删除原唯一约束
ALTER TABLE MyTable DROP CONSTRAINT "MyTable-ColumnID1-ColumnName-Unique-Key";

-- 创建可延迟的唯一约束
ALTER TABLE MyTable ADD CONSTRAINT "MyTable-ColumnID1-ColumnName-Unique-Key" 
UNIQUE (ColumnID1, ColumnName) DEFERRABLE INITIALLY DEFERRED;

之后执行Upsert语句（或用事务包裹确保约束检查延迟到提交）：

BEGIN;
insert into MyTable (ColumnID1, ColumnID2, ColumnName)
values (1, 1, 'd'), (1, 2, 'c')
on conflict (ColumnID1, ColumnID2)
do update
set
    ColumnName = EXCLUDED.ColumnName;
COMMIT;

方法2：直接使用UPDATE语句（无需修改约束） #

若不想修改表结构，可使用UPDATE配合CTE批量更新，避免逐行检查约束的问题：

WITH new_values AS (
    VALUES 
        (1::integer, 1::integer, 'd'::varchar(1000)),
        (1::integer, 2::integer, 'c'::varchar(1000))
)
UPDATE MyTable t
SET ColumnName = nv.column3
FROM new_values nv
WHERE t.ColumnID1 = nv.column1 
  AND t.ColumnID2 = nv.column2;

这种方式会一次性计算所有更新后的值再应用到表中，不会触发中间状态的约束冲突。

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