0

I have the following PG (12) reservations table

id               uuid
reservable_id    uuid
reserved_between tsrange    

How can I ensure there are no 2 reservations for the same reservable_id overlapping each other?

A "simple" UNIQUE INDEX on reservable_id,reserved_between works only (as expected) only with equal ranges.

  • Range Functions and Operators, overlap operator &&. Use in trigger logic. "Currently, CHECK expressions cannot contain subqueries nor refer to variables other than columns of the current row." – Akina Jan 23 at 5:40
1

You need the btree_gist extension so that you can create a GiST index on an uuid:

CREATE EXTENSION btree_gist;

Now you can create an exclusion constraint:

ALTER TABLE reservations ADD EXCLUDE USING gist (
   reservable_id WITH =,
   reserved_between WITH &&
);

&& is the “overlaps” operator for ranges.

| improve this answer | |
  • Hi Laurentz, That is exactly what I was looking for. Thanks for the help. – Sig Jan 23 at 8:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.