Postgres Exclusion Constraints with GiST Indexes

Standard unique constraints check equality. Sometimes you need to enforce that two rows don't overlap — for example, no two bookings for the same room during the same time period.

Postgres handles this with exclusion constraints backed by a GiST index.

CREATE EXTENSION btree_gist;


CREATE TABLE bookings (
  room_id   int,
  period    tsrange,
  EXCLUDE USING gist (
    room_id WITH =,
    period  WITH &&
  )
);

This says: no two rows may have the same room_id AND overlapping period. The && operator means "overlaps" for range types.

Trying to insert a conflicting booking raises a constraint violation — enforced at the database level, not in application code.

Reference: Postgres docs