postgres中的可延遲唯一索引

查看alter table 的 postgres 文件，似乎可以將正常約束標記為DEFERRABLE（更具體地說，INITIALLY DEFERRED這是我感興趣的）。

索引也可以與約束相關聯，只要：

索引不能有表達式列，也不能是部分索引

這讓我相信目前沒有辦法擁有一個具有條件的唯一索引，例如：

CREATE UNIQUE INDEX unique_booking
 ON public.booking
 USING btree
 (check_in, check_out)
 WHERE booking_status = 1;

是INITIALLY DEFERRED，意思是，唯一性“約束”只會在交易結束時被驗證（如果SET CONSTRAINTS ALL DEFERRED;使用的話）。

我的假設是否正確，如果是，有什麼方法可以實現預期的行為？

謝謝

一個索引不能被推遲——不管它是否UNIQUE，部分或不是，只是一個UNIQUE約束。其他類型的約束 ( FOREIGN KEY, PRIMARY KEY, EXCLUDE) 也是可延遲的 - 但不是CHECK約束。

因此，將在每條語句（實際上是在目前實現中的每一行插入/更新之後）檢查唯一部分索引（及其實現的隱式約束），而不是在事務結束時檢查。

---

如果您想將此約束實現為可延遲的，您可以做的是在設計中再添加一個表。像這樣的東西：

CREATE TABLE public.booking_status
 ( booking_id int NOT NULL,               -- same types
   check_in timestamp NOT NULL,           -- as in  
   check_out timestamp NOT NULL,          -- booking
   CONSTRAINT unique_booking
       UNIQUE (check_in, check_out)
       DEFERRABLE INITIALLY DEFERRED,
   CONSTRAINT unique_booking_fk
       FOREIGN KEY (booking_id, check_in, check_out)
       REFERENCES public.booking (booking_id, check_in, check_out)
       DEFERRABLE INITIALLY DEFERRED
 ) ;

使用這種設計並假設booking_status只有 2 個可能的選項（0 和 1），您可以將其完全從中刪除booking（如果 有一行booking_status，則為 1，否則為 0）。

---

另一種方法是（ab）使用EXCLUDE約束：

ALTER TABLE booking
   ADD CONSTRAINT unique_booking
       EXCLUDE 
         ( check_in  WITH =, 
           check_out WITH =, 
           (CASE WHEN booking_status = 1 THEN TRUE END) WITH =
         ) 
       DEFERRABLE INITIALLY DEFERRED ;

在**dbfiddle**測試。

以上是做什麼的：

• CASE表達式變為NULLwhenbooking_status為 null 或不同於 1。我們可以寫得(CASE WHEN booking_status = 1 THEN TRUE END)好像這樣(booking_status = 1 OR NULL)更清楚。
• 唯一和排除約束接受一個或多個表達式為 NULL 的行。所以它作為一個過濾索引WHERE booking_status = 1。
• 所有的WITH運算符都是=這樣，它充當了一個UNIQUE約束。
• 這兩個組合使約束充當過濾的唯一索引。
• 但這是一個約束，EXCLUDE可以推遲約束。

---

上述方法的改進（對Denis Ryzhkov的改進）是使用部分（過濾的）EXCLUDE 約束。使用更少的空間（與部分索引相同）並且可以延遲：

ALTER TABLE booking
   ADD CONSTRAINT unique_booking
       EXCLUDE 
         ( check_in  WITH =, 
           check_out WITH =
         ) 
       WHERE (booking_status = 1)
       DEFERRABLE INITIALLY DEFERRED ;

在**dbfiddle-2**測試。

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