Deferrable unique index in postgres

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

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.

StackExchange Database Administrators Q#166082, answer score: 24