PostgreSQL：更新主键，避免冲突 [英] PostgreSQL: Update primary key, avoid conflict

问题描述

我想更新表的几行中的主键。如果所有行均已更新，则密钥将再次唯一，但是第一行的更新将导致与第二行的密钥暂时冲突。有解决这个问题的优雅方法吗？

I want to update the primary key in several rows of a table. If all rows were updated, the key would be unique again, but the update of the first row results in a temporary conflict with the key of the second row. Is there an elegant way to solve this?

示例：

create table erichtest ( i integer, v varchar(200) );
alter table erichtest add constraint pk_erichtest primary key(i);
insert into erichtest values(1, 'Eins');
insert into erichtest values(2, 'Zwei');
update erichtest set i=i+1;

错误：重复的键值违反了唯一约束 pk_erichtest

ERROR: duplicate key value violates unique constraint "pk_erichtest"

推荐答案

类似的东西应该会帮助：

b=# begin;
BEGIN
b=# alter table erichtest drop constraint pk_erichtest ;
ALTER TABLE
b=#  alter table erichtest add constraint pk_erichtest primary key (i) DEFERRABLE INITIALLY IMMEDIATE;
ALTER TABLE
b=# set constraints pk_erichtest deferred ;
SET CONSTRAINTS
b=# update erichtest set i=i+1;
UPDATE 2
b=# select * from erichtest ;
 i |  v
---+------
 2 | Eins
 3 | Zwei
(2 rows)

b=# end;
COMMIT

这篇关于PostgreSQL：更新主键，避免冲突的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！