TopolearnDouble bookings are a classic race condition that an exclusion constraint prevents in one line.
A range type stores an interval of values as one column: int4range, numrange, daterange, tstzrange. Bounds can be inclusive [ or exclusive ); the convention [start, end) makes adjacent ranges meet without overlapping. Since PostgreSQL 14, multiranges hold sets of non-contiguous ranges.
Operators make interval logic simple and correct:
&&overlaps,@>contains a value or range,<@is contained by.-|-adjacent,*intersection,+union.lower(r),upper(r),isempty(r).
The killer feature is the exclusion constraint: EXCLUDE USING gist (room WITH =, during WITH &&) means "no two rows may have equal room and overlapping during". Like a unique constraint, it is enforced atomically, so two concurrent bookings for the same slot can't both succeed — something application checks can't guarantee without locking. Equality on a plain column inside a GiST index needs the btree_gist extension.
Ranges are useful for bookings, price validity periods, shift schedules and versioned (temporal) data. PostgreSQL 18 adds temporal PRIMARY KEY/UNIQUE constraints with WITHOUT OVERLAPS, building on the same idea.
Example
CREATE EXTENSION IF NOT EXISTS btree_gist;
CREATE TABLE booking (
room int NOT NULL,
during tstzrange NOT NULL,
EXCLUDE USING gist (room WITH =, during WITH &&)
);
INSERT INTO booking VALUES (1, '[2026-06-01 10:00+00, 2026-06-01 12:00+00)');
INSERT INTO booking VALUES (1, '[2026-06-01 12:00+00, 2026-06-01 13:00+00)'); -- ok, adjacent
INSERT INTO booking VALUES (2, '[2026-06-01 11:00+00, 2026-06-01 12:00+00)'); -- ok, other room
INSERT INTO booking VALUES (1, '[2026-06-01 11:30+00, 2026-06-01 12:30+00)');
-- ERROR: conflicting key value violates exclusion constraint "booking_room_during_excl"
SELECT room FROM booking
WHERE during @> '2026-06-01 11:15+00'::timestamptz ORDER BY room; -- 1, 2Remember
- Range types store intervals; use [start, end) bounds
- && tests overlap, @> tests containment
- EXCLUDE USING gist prevents overlapping rows atomically
Watch out for
- Storing start and end in two columns and checking overlaps in application code, which races.