如何同時更新 PostgreSQL 中的唯一鍵？

CREATE TABLE widget (
 id serial PRIMARY KEY,
 name text NOT NULL,
 ordinal int NOT NULL UNIQUE
);

我有數據

id | name | ordinal 
----+------+---------
 1 | A    |       1
 2 | B    |       2
 3 | C    |       3

我想將其更新為

id | name | ordinal 
----+------+---------
 1 | A    |       3
 2 | B    |       2
 3 | C    |       1

盡可能少地接觸記錄（即不要重寫整個記錄集，不要啟動不必要的觸發器），更新ordinal為目標值的普遍適用的方法是什麼？

普通更新只會讓我違反約束，即使它發生在單個語句中。

並且刪除和重新創建唯一約束是昂貴的並且並發性差。

這似乎是一個足夠普遍的問題，應該有一個很好的方法來做到這一點，我只是想不出。

將約束定義為可延遲的：

CREATE TABLE widget (
 id serial PRIMARY KEY,
 name text NOT NULL,
 ordinal int NOT NULL UNIQUE DEFERRABLE
);

然後你可以在一個語句中更新它：

update widget
 set ordinal = t.new_ordinal
from (
 values (1, 3), (3,1) 
) as t(id, new_ordinal)
where t.id = widget.id   

預設情況下是普通UNIQUE約束NOT DEFERRABLE。在每一行之後檢查唯一的違規行為。手冊將其表述為：

每個命令後立即檢查

但它確實檢查了每個單獨的書面行。

如果您定義UNIQUE約束DEFERRABLE（如提供的 a_horse），則會在每個 statement 之後檢查唯一的違規行為。在這方面，同一查詢中的多個 CTE 仍算作一條語句。

CREATE TABLE widget_deferrable (
 id serial PRIMARY KEY,
 ordinal int NOT NULL UNIQUE DEFERRABLE  -- !
);

如果您還實際將約束設置為DEFERRED，則對唯一違規的檢查將推遲到每個事務之後。

CREATE TABLE widget_deferred (
 id serial PRIMARY KEY,
 ordinal int NOT NULL UNIQUE DEFERRABLE INITIALLY DEFERRED  --!
);

綜合展示：

db<>在這裡擺弄

進一步閱讀：

• 約束定義 DEFERRABLE INITIALLY IMMEDIATE 仍然是 DEFERRED？

引用自：https://dba.stackexchange.com/questions/285910