sql - 如何在现有位置插入新行之前更新唯一的表行号

Question

SQL 表:

 id |  name
----+--------
  1 | apple
  2 | orange
  3 | apricot

id是主键，唯一的，可以是SERIES。目标是在 id 等于 2 的位置插入新行，并将下面的现有行号(即 2 和 3)移动到 3 和 4 位置。

我在插入新行之前试过换行:

"UPDATE some_table SET id = id + 1 WHERE id >= id"

但是发生错误:

org.postgresql.util.PSQLException: ERROR: duplicate key value violates unique constraint "some_table_pkey"
  Detail: Key (id)=(3) already exists.

有没有一些有效的方法来做这样的操作？

表格更新后应该是这样的:

 id |  name
----+--------
  1 | apple
  2 | cherry
  3 | orange
  4 | apricot

Answer 1

虽然我认为这种尝试是徒劳的，但您可以通过将主键约束标记为可延迟来实现:

CREATE TABLE some_table
(
  id int, 
  name text
);

alter table some_table 
  add constraint pk_some_table 
  primary key (id) 
  deferrable initially immediate; --<< HERE

在这种情况下，PK 约束是按语句计算的，而不是按行计算的。

在线示例:https://rextester.com/JSIV60771