How do you stop two bookings from overlapping in PostgreSQL?
Use an exclusion constraint with a range column and the overlap operator && on a GiST index. The database then rejects any row whose range overlaps an existing row for the same resource.
Explain it simply
A seminar hall cannot be booked by two clubs at the same time. A UNIQUE constraint only catches identical values, and "10:00-12:00" and "11:00-13:00" are different yet overlapping. An exclusion constraint says "no two rows may satisfy this operator on these columns together", for example "same hall AND overlapping time".
It is enforced inside the database, so no race between two simultaneous bookings can sneak through, unlike an application-level check.
Example
CREATE EXTENSION IF NOT EXISTS btree_gist; -- lets gist handle the = on hall
CREATE TABLE hall_bookings (
hall text,
club text,
during tstzrange,
EXCLUDE USING gist (hall WITH =, during WITH &&)
);
INSERT INTO hall_bookings VALUES
('Main', 'Coding', '[2026-10-10 10:00, 2026-10-10 12:00)');
INSERT INTO hall_bookings VALUES
('Main', 'Drama', '[2026-10-10 11:00, 2026-10-10 13:00)');
-- ERROR: conflicting key value violates exclusion constraint
INSERT INTO hall_bookings VALUES
('Main', 'Music', '[2026-10-10 12:00, 2026-10-10 14:00)'); -- ok: ends at 12:00 exclusiveThe [ and ) mean start-inclusive, end-exclusive, so back-to-back bookings are allowed.
What to say in the interview
"An exclusion constraint generalises UNIQUE to any operator. For no overlapping bookings I use a range column with && on a GiST index, plus btree_gist for the equality part. It is race-free because the database enforces it."
Common mistakes
- Checking for overlaps in application code before inserting, which is not safe under concurrency.