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 becomesNULL
whenbooking_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 aUNIQUE
constraint.These two combined make the constraint act as a filtered unique index.
But it's a constraint and
EXCLUDE
constraints can be deferred.