在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)

请注意，在category2中，(foo4, bar4)在(foo3, bar3)之前。 现在，如果我想重新排列一个类别中的项目，我需要更新category_position...但由于主键的存在，我不能像使用SQL Server那样使用PostgreSQL来移动值。

- personne3000

Answer 1

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

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

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在需要时推迟约束评估。

---

然而，真正的问题是：为什么需要在主键值上这样做？主键值没有任何意义，因此似乎无需增加所有值的值（无论使用哪个DBMS）。

- user330315

---

谢谢您的回答；我在我的问题中添加了一个更完整的场景。我会查看您的解决方案。 - personne3000

@personne3000：deferrable属性适用于_constraint_级别，因此PK有多少列并不重要。但是将位置作为PK的一部分似乎是一个相当奇怪的设计。PK值应该是稳定的，不应频繁更改。每次插入新项都必须更改PK值通常表示数据库设计存在问题。 - user330315

然而，我仍然希望在这些列上保持唯一约束，因为我不希望同一类别中有两个项目具有相同的位置。我有另一个候选元组用于pkey（其中有另一个唯一约束）; 是否最好使用它代替？ - personne3000

@personne3000：如果您有一个“稳定”的主键列对，那么我更喜欢使用它。您仍然可以在category_id/position上定义可延迟的唯一约束（而不是索引！）。但是，如果您经常重新编号位置，为什么需要首先具有唯一约束呢？当您重新编号时，可以为每个项目分配唯一位置。 - user330315

最终我使用了另一个元组作为主键，并像你建议的那样，使用了可延迟的唯一约束来限制类别和顺序。顺序不会经常更新：只有当管理员决定更改某些项目的显示顺序时才会更新。我更喜欢保持唯一约束，以确保我的数据在数据库层面上是有效的。谢谢！ - personne3000

Answer 2

不改变约束条件的解决方案为 deferrable initially immediate。

UPDATE tbl_test t1 
SET    testkey = t2.testkey + 1 
FROM   (SELECT testkey 
    FROM   tbl_test 
    ORDER  BY testkey DESC) t2 
WHERE  t1.testkey = t2.testkey 

在线示例：http://rextester.com/edit/GMJ48099

- razon

---

如果表格足够大，它会生成“ERROR: too many range table entries”错误。有什么办法可以克服这个问题吗？ - Meir Tseitlin