在PostgreSQL中移动（更新）唯一列值

Question

使用MS SQL Server，以下工作正常：

CREATE TABLE #temptable(mykey int primary key)

INSERT INTO #temptable VALUES (1)
INSERT INTO #temptable VALUES (2)

UPDATE #temptable SET mykey=mykey+1

但是，使用PostgreSQL，以下操作将失败：

CREATE TABLE pg_temp.tbl_test(testkey integer primary key)

INSERT INTO pg_temp.tbl_test VALUES (1)
INSERT INTO pg_temp.tbl_test VALUES (2)

UPDATE pg_temp.tbl_test SET testkey=testkey+1

错误：重复的键值违反了唯一约束“ tbl_test_pkey”详细信息：键（testkey）=（2）已存在。

我需要在一个表中增加一列的每个值，这是复合唯一约束的一部分。如何在一份声明中做到这一点？

谢谢 ！

编辑：如果您想知道为什么这样做（至少对我而言），那么这是一个更完整的方案。

我有一张按类别组织的项目表。每个项目在类别中都有特定的位置。

category_id (PK) | category_position (PK) | item_attribute_1 | item_attribute_2
1 | 1 | foo | bar
1 | 2 | foo2 | bar2
2 | 1 | foo4 | bar4
2 | 2 | foo3 | bar3

该表包含如下数据：

category1 : (foo, bar), (foo2, bar2)
category2 : (foo4, bar4), (foo3, bar3)

请注意，类别2中的（foo4，bar4）在（foo3，bar3）之前。现在，如果要对一个类别中的项目重新排序，则需要更新category_position
…但是由于PK，我无法像使用SQL Server一样使用PostgreSQL来移动值。

Answer 1

这确实有点令人困惑，因为在DML操作期间，所有其他约束都是在语句级别上评估的，仅PK /唯一约束是在每行级别上评估的。

但是，您可以通过将主键约束声明为可延迟来解决此问题：

create table tbl_test 
(
  testkey   INTEGER,
  constraint pk_tbl_test primary key (testkey) deferrable initially immediate
);

insert into tbl_test values (1), (2);

set constraints all deferred;

update tbl_test
   set testkey = testkey +1;

延迟约束确实有一些开销，因此可以通过将其定义为initially immediate将开销最小化来进行定义。您可以使用在需要时推迟约束评估set constraint。

但是，真正的问题是：为什么要对主键值执行此操作？PK值毫无意义，因此似乎没有必要增加所有值（无论使用的是DBMS如何）