Deferrable unique index in postgres

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 way 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.