Deferrable unique index in postgres

Question

Looking into postgres documentation for alter table, it seems regular constrains can be marked as DEFERRABLE (more concretely, INITIALLY DEFERRED, which is what I'm interested in).

Indexes can also be associated with a constraint, as long as:

The index cannot have expression columns nor be a partial index

Which leads me to believe there is currently no way to have a unique index with conditions, like:

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

To be INITIALLY DEFERRED, meaning, that the uniqueness 'constraint' will only be verified on the end of the transaction (if SET CONSTRAINTS ALL DEFERRED; is used).

Is my assumption correct, and if so, is there any way to achieve the intended behaviour?

Thanks

Answer 1

A index cannot be deferred - doesn't matter if it is UNIQUE or not, partial or not, only a UNIQUE constraint. Other types of constraints (FOREIGN KEY, PRIMARY KEY, EXCLUDE) are also deferrable - but not CHECK constraints.

So the unique partial index (and the implicit constraint it implements) will be checked at every statement (and in fact after every row insert/update in current implementation), not at the end of transaction.

---

What you could do, if you want to implement this constraint as deferrable, is to add one more table in the design. Something like this:

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

With this design and assuming that booking_status has only 2 possible options (0 and 1), you could remove it entirely from booking (if there is a row at booking_status, it's 1, if not is 0).

---

Another method would be to (ab)use an EXCLUDE constraint:

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 ;

Tested at dbfiddle.

What the above does:

• The CASE expression becomes NULL when booking_status is null or different than 1. We could write (CASE WHEN booking_status = 1 THEN TRUE END) as (booking_status = 1 OR NULL) if that makes it any more clear.

• Unique and exclude constraints accept rows where one or more of the expressions is NULL. So it acts as a filtered index with WHERE booking_status = 1.

• All the WITH operators are = so it acts as a UNIQUE constraint.

• These two combined make the constraint act as a filtered unique index.

• But it's a constraint and EXCLUDE constraints can be deferred.

---

An improvement of the above method (thnx to Denis Ryzhkov) is to use a partial (filtered) EXCLUDE constraint. Uses less space (same way as a partial index) and is deferrable:

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

Tested at dbfiddle-2.

Answer 2

Although the years of this question have passed, I would like to clarify for Spanish speakers, the tests have been done in Postgres:

The following constraint was added to a table of 1337 records, where the kit is the primary key:

**Bloque 1**
ALTER TABLE ele_kitscompletos
ADD CONSTRAINT unique_div_nkit
PRIMARY KEY (div_nkit) 

This creates a default primary key NOT DEFERRED for the table so when trying the next UPDATE we get error:

update ele_kitscompletos
set div_nkit = div_nkit + 1; 

ERROR: duplicate key violates uniqueness restriction «unique_div_nkit»

In Postgres, executing an UPDATE for each ROW verifies that the RESTRICTION or CONSTRAINT is met.

---

The CONSTRAINT IMMEDIATE is now created and each statement is executed separately:

ALTER TABLE ele_kitscompletos
ADD CONSTRAINT unique_div_nkit
PRIMARY KEY (div_nkit)
DEFERRABLE INITIALLY IMMEDIATE

**Bloque 2**
BEGIN;   
UPDATE ele_kitscompletos set div_nkit = div_nkit + 1;
INSERT INTO public.ele_kitscompletos(div_nkit, otro_campo)
VALUES 
  (1338, '888150502');
COMMIT;

Query OK, 0 rows affected (execution time: 0 ms; total time: 0 ms) Query OK, 1328 rows affected (execution time: 858 ms; total time: 858 ms) ERROR: llave duplicada viola restricción de unicidad «unique_div_nkit» DETAIL: Ya existe la llave (div_nkit)=(1338).

Here SI allows changing the primary key since it executes the entire first complete sentence (1328 rows); but although it is in transaction (BEGIN), the CONSTRAINT is validated immediately upon finishing each sentence without having made COMMIT, therefore generates the error when executing the INSERT. Finally we created the CONSTRAINT DEFERRED do the following:

**Bloque 3**
ALTER TABLE public.ele_edivipol
DROP CONSTRAINT unique_div_nkit RESTRICT;   

ALTER TABLE ele_edivipol
ADD CONSTRAINT unique_div_nkit
PRIMARY KEY (div_nkit)
DEFERRABLE INITIALLY DEFERRED

If we execute each statement of ** Block 2 **, each sentence separately, no error is generated to the INSERT since it does not validate but the final COMMIT is executed where it finds an inconsistency.

---

For complete information in English I suggest you check the links:

Deferrable SQL Constraints in Depth

NOT DEFERRABLE versus DEFERRABLE INITIALLY IMMEDIATE