skip to content

In PostgreSQL, how do you make the database itself reject overlapping meeting-room bookings, and why does the usual EXCLUDE constraint need btree_gist?

level: middleimportance: must knowfreq 58%

answer

  1. one range column per booking
  2. an operator per compared column
  3. scalar equality inside GiST
  4. half-open bounds for back-to-back
  5. partial predicate for cancellations

basics

~20 s

Store each booking's time as a tstzrange and declare EXCLUDE USING gist (room_id WITH =, during WITH &&). Core GiST has no equality operator class for scalar types such as bigint, so the btree_gist extension supplies one for room_id.

solid answer

~50 s

Model the slot as a `tstzrange` column and add `EXCLUDE USING gist (room_id WITH =, during WITH &&)`. PostgreSQL then rejects any row for which another row has an equal `room_id` **and** an overlapping range, raising `23P01`. The constraint builds a GiST index, because the check needs an index that can search by `&&`; core GiST has an operator class for ranges but none for `=` on `bigint`, so you install the trusted contrib extension `btree_gist`, which adds B-tree-like GiST operator classes for scalar types. Make it airtight with `NOT NULL` on both columns (a NULL comparison never conflicts), `CHECK (NOT isempty(during))` (an empty range overlaps nothing) and the default `[)` bounds, so back-to-back meetings do not clash. A `WHERE (cancelled_at IS NULL)` predicate lets cancelled bookings release their slot. Unlike a pre-insert `SELECT`, the check holds under concurrency.

code

sql · 14 lines
sql
INSERT INTO booking (room_id, during)
VALUES (7, tstzrange('2026-10-06 09:00+00', '2026-10-06 10:00+00'));

-- back-to-back: [10:00, 11:00) shares no instant with [09:00, 10:00)
INSERT INTO booking (room_id, during)
VALUES (7, tstzrange('2026-10-06 10:00+00', '2026-10-06 11:00+00'));

-- overlaps 09:00-10:00 in room 7: fails with SQLSTATE 23P01
INSERT INTO booking (room_id, during)
VALUES (7, tstzrange('2026-10-06 09:30+00', '2026-10-06 10:30+00'));

-- same time, different room: allowed
INSERT INTO booking (room_id, during)
VALUES (8, tstzrange('2026-10-06 09:30+00', '2026-10-06 10:30+00'));

go deeper

for a junior

Recall the shape: a tstzrange column, EXCLUDE USING gist with room_id WITH = and during WITH &&, and CREATE EXTENSION btree_gist beforehand.

for a middle

Explain why UNIQUE cannot express overlap, why GiST needs btree_gist for the scalar column, and how NULLs, empty ranges and [) bounds decide whether the rule really holds.

for a senior

Show production judgement: partial predicates for cancelled bookings, the deadlock-then-23P01 behaviour under concurrency, and what adding the constraint to an existing busy table costs.

for a principal

Frame the choice between an EXCLUDE constraint, an 18 temporal key and application-side scheduling logic by who could bypass the rule and what each costs as booking volume grows.

## Why UNIQUE cannot say "no overlap" A `UNIQUE` constraint compares rows with **equality** only. Two bookings for room 7, 09:00 to 10:00 and 09:30 to 10:30, have different start and end times, so no combination of their columns is ever equal, yet the room is double-booked. The rule you need is "no two rows with the same room whose time ranges overlap": one column compared with `=`, another with an overlap operator. PostgreSQL expresses that directly with an **exclusion constraint**, a PostgreSQL extension to SQL. ## The EXCLUDE constraint An exclusion constraint lists pairs of *column or expression* `WITH` *operator*, and guarantees that for any two rows **not all** of those comparisons return true. If every operator is `=`, it degenerates to a unique constraint, and a real `UNIQUE` would be faster. For bookings: ```sql CREATE EXTENSION IF NOT EXISTS btree_gist; CREATE TABLE booking ( booking_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, room_id bigint NOT NULL REFERENCES room (room_id), during tstzrange NOT NULL CHECK (NOT isempty(during)), cancelled_at timestamptz, CONSTRAINT booking_no_overlap EXCLUDE USING gist (room_id WITH =, during WITH &&) WHERE (cancelled_at IS NULL) ); ``` What each part does: - **`tstzrange`** stores the slot as one value, a range of `timestamptz`, so overlap is a single operator, `&&`. - The constraint creates an index with the **same name as the constraint**, of the method you name, and every insert or update probes it for conflicting rows. - The operators must be **commutative**, and each must belong to an operator class of that index method. - The method must support plain index scans (`amgettuple`), which rules out GIN; in practice exclusion constraints use **GiST** or **SP-GiST**. ## Why btree_gist The `during WITH &&` element is covered by GiST's built-in `range_ops` operator class. The `room_id WITH =` element is not: core GiST ships no operator class for equality on `bigint`, `uuid`, `text` and similar scalar types, so without help `CREATE TABLE` fails with `data type bigint has no default operator class for access method "gist"`. The contrib extension **`btree_gist`** supplies GiST operator classes with B-tree behaviour for `int2`, `int4`, `int8`, `numeric`, `timestamptz`, `date`, `text`, `uuid`, `bool`, enums and more, so the scalar column and the range column can live in one multicolumn GiST index. It is a **trusted** extension: a non-superuser with `CREATE` privilege on the database can install it. It also adds index support for `<>`, useful for rules such as "same room, different organiser". ## Details that make the rule airtight 1. **`NOT NULL` on every compared column.** A comparison involving NULL is not true, so a row with a NULL `room_id` or `during` never conflicts with anything. 2. **Reject empty ranges.** `tstzrange(t, t)` is empty, and an empty range overlaps nothing, so it would slip past; `CHECK (NOT isempty(during))` closes the hole. 3. **Use half-open bounds.** The two-argument constructor `tstzrange(start, end)` builds `[start, end)`, so a meeting ending at 10:00 and one starting at 10:00 are merely adjacent and do not overlap. 4. **`timestamptz`, not `timestamp`.** Bookings are instants; a zone-less range lets two offices disagree about what overlaps. 5. **A predicate for soft states.** `WHERE (cancelled_at IS NULL)` builds a partial index, so cancelled bookings stop blocking their slot. The parentheses around the predicate are required. ## Why not a trigger or an application check? A `SELECT ... WHERE room_id = 7 AND during && $1` followed by an `INSERT` lets two concurrent transactions both see a free slot under the default READ COMMITTED isolation. The exclusion check instead finds conflicting rows even when their transactions are still in progress, waits for them, and re-checks, so at most one of two overlapping bookings commits. Why constraints beat application checks in general is a relational-constraints topic. ## Alternatives and costs | Approach | Safe under concurrency | Notes | |---|---|---| | `EXCLUDE USING gist` with btree_gist | yes | every supported release; partial `WHERE` allowed | | `PRIMARY KEY`/`UNIQUE ... WITHOUT OVERLAPS` | yes | PostgreSQL 18; enforced the same way; can be a temporal FK target | | Trigger that locks the room row first | only if every writer goes through it | more code, easy to bypass | | Application pre-check | no | races under READ COMMITTED | Two operational notes. An exclusion constraint cannot be added `NOT VALID`, and adding one to a populated table builds the GiST index inside `ALTER TABLE` under an `ACCESS EXCLUSIVE` lock; planning that is the schema-changes topic. And `INSERT ... ON CONFLICT DO UPDATE` cannot use an exclusion constraint as its arbiter.

  • Why does a PostgreSQL EXCLUDE constraint on a nullable room_id fail to stop double bookings?
    An exclusion constraint rejects a pair of rows only when every listed comparison returns true. `NULL = 7` yields null, not true, so a booking with a NULL `room_id` or NULL `during` never conflicts, and any number of them coexist. Declare compared columns `NOT NULL`, and add `CHECK (NOT isempty(during))`, because an empty range overlaps nothing either.
  • How would you apply the PostgreSQL no-overlap rule only to confirmed bookings?
    Add a predicate: `EXCLUDE USING gist (room_id WITH =, during WITH &&) WHERE (status = 'confirmed')`. PostgreSQL builds a partial GiST index, and only rows matching the predicate take part, so tentative or cancelled rows neither block nor are blocked. The predicate needs its parentheses and must use only immutable functions, as for any partial index.
  • In PostgreSQL 18, when would you choose UNIQUE (room_id, during WITHOUT OVERLAPS) over the EXCLUDE form?
    When the non-overlap rule is a key other tables should reference. The 18 temporal key is enforced like `EXCLUDE USING gist (room_id WITH =, during WITH &&)` but is catalogued as a unique or primary key, so a `FOREIGN KEY ... PERIOD` can point at it. EXCLUDE remains the tool for a partial `WHERE` predicate, `<>`, or operators other than `=` and `&&`.

A reception desk keeps a wall planner with one column per room. A clerk pencils in a booking only after running a finger down that room's column and finding no block that overlaps; blocks in other rooms' columns never matter. The EXCLUDE constraint is that rule built into the planner itself, and the GiST index is the finger that finds overlapping blocks quickly.

  • A UNIQUE constraint on (room_id, start_time, end_time) prevents overlapping bookings.
  • Exclusion constraints can use a GIN index, since GIN supports the && operator.
  • btree_gist is needed for the range column, not for the room_id column.
  • Back-to-back meetings always conflict because their ranges share an endpoint.
  • A SELECT for overlaps before the INSERT is as safe as the constraint.