(opens in a new tab)

Postgres EXCLUDE Constraints for Overlaps

August 24, 2026 by Chat2DB Team

Every booking system eventually has to answer the same question: can two rows exist in this table at the same time, for the same resource, covering overlapping time ranges? A meeting room scheduler, a car rental system, a hotel reservation table, a piece of equipment that only one team can use at once — they are all instances of the same problem, and most applications solve it wrong. PostgreSQL has a purpose-built tool for it, the EXCLUDE constraint, that turns "no two overlapping bookings for the same room" into a rule the database enforces the same way it enforces UNIQUE or NOT NULL. This guide covers how it works, how to combine it with range types and btree_gist, how to scope it with a partial WHERE clause, and how to defer it inside a transaction.

The naive approach and its race condition

The instinctive way to prevent double-booking is entirely in application code: before inserting a new booking, run a query that checks whether any existing row for the same room overlaps the requested time range, and only insert if nothing comes back.

-- Step 1: check for a conflict
SELECT id FROM bookings
WHERE room_id = 12
  AND during && '[2026-09-01 09:00, 2026-09-01 10:00)'::tstzrange;
 
-- Step 2: if the check returned no rows, insert
INSERT INTO bookings (room_id, during) VALUES
  (12, '[2026-09-01 09:00, 2026-09-01 10:00)');

This looks reasonable and works fine under light load, but it has a classic time-of-check-to-time-of-use race condition. Two concurrent requests for the same room and overlapping time slots can both run the SELECT at roughly the same moment, both see zero conflicting rows because neither has committed its INSERT yet, and both proceed to insert. The result is two bookings for the same room at the same time, which is exactly the outcome the check was supposed to prevent. Wrapping the two statements in a transaction does not fix this on its own, because the default read committed isolation level does not stop a second transaction from reading the "before" state while the first transaction's insert is still in flight. You can work around it with explicit locking, but that pushes correctness onto every piece of code that ever writes to the table, which is fragile and easy to get wrong as the codebase grows.

EXCLUDE constraints: UNIQUE for arbitrary operators

UNIQUE constraints say "no two rows may have the same value in this column," where "same" specifically means the = operator. An EXCLUDE constraint generalizes that idea to any operator: "no two rows may satisfy this operator when compared on this column." Instead of being limited to equality, you tell Postgres which operator counts as a conflict, and it checks that condition against every existing row atomically, as part of the same index operation that would normally just look for exact duplicates. Because the check and the insert happen inside one atomic operation at the storage level, there is no window between "check" and "use" for a second transaction to slip through. Two concurrent inserts that would overlap are serialized by the index itself, and whichever one loses the race gets an error instead of a silently corrupted schedule.

The operator most relevant to scheduling problems is &&, the overlap operator defined on PostgreSQL's range types. An EXCLUDE constraint using && reads naturally: "no two rows for the same room may have overlapping time ranges."

Range types and the half-open interval

PostgreSQL ships built-in range types, including tstzrange for timestamp ranges with a time zone, tsrange for local timestamps, and daterange for whole days. A range value is written as a lower and upper bound with bracket characters indicating whether each end is inclusive ([, ]) or exclusive ((, )). The default constructor for these types, and the convention used almost everywhere in scheduling code, is [): the lower bound is inclusive and the upper bound is exclusive.

This detail matters more than it looks like it should. If a 9:00-10:00 meeting is stored as [2026-09-01 09:00, 2026-09-01 10:00) and the next meeting starts at 10:00, stored as [2026-09-01 10:00, 2026-09-01 11:00), the two ranges touch but do not overlap, because the first range's exclusive upper bound of 10:00 is not actually included in it. The && operator correctly reports false for this pair, so back-to-back bookings with no gap are allowed, which is exactly what you want. If you instead used inclusive bounds on both ends ([]), those two adjacent bookings would be reported as overlapping and the constraint would reject a perfectly legitimate schedule. Always let ranges default to [), and be careful with any code that manually constructs ranges with explicit bound characters.

A full worked example

Start with a table that stores a room and a time range per booking:

CREATE TABLE bookings (
  id      bigserial PRIMARY KEY,
  room_id int NOT NULL,
  during  tstzrange NOT NULL
);

EXCLUDE constraints are implemented on top of a GiST (or SP-GiST) index, because that is the index type that knows how to evaluate operators like && efficiently. Range types already support GiST natively, but room_id is a plain integer, and GiST does not know how to index a plain = comparison on an integer out of the box. The btree_gist extension bridges that gap: it teaches GiST how to handle ordinary B-tree-style operators, including = on integers, text, dates and more, so they can sit alongside a range column in the same multi-column GiST index.

CREATE EXTENSION IF NOT EXISTS btree_gist;

With the extension installed, add the constraint:

ALTER TABLE bookings
  ADD CONSTRAINT bookings_no_overlap
  EXCLUDE USING gist (room_id WITH =, during WITH &&);

This reads as: for any two rows, if their room_id values are equal and their during ranges overlap, reject the second one. Each column gets its own operator, combined with a logical AND across all of them. Insert a booking, then try to insert a conflicting one for the same room:

INSERT INTO bookings (room_id, during) VALUES
  (12, '[2026-09-01 09:00+00, 2026-09-01 10:00+00)');
 
INSERT INTO bookings (room_id, during) VALUES
  (12, '[2026-09-01 09:30+00, 2026-09-01 10:30+00)');
-- ERROR:  conflicting key value violates exclusion constraint "bookings_no_overlap"
-- DETAIL:  Key (room_id, during)=(12, ["2026-09-01 09:30:00+00","2026-09-01 10:30:00+00")) conflicts with existing key (room_id, during)=(12, ["2026-09-01 09:00:00+00","2026-09-01 10:00:00+00")).

The second INSERT is rejected outright, at the database level, regardless of how many concurrent clients are trying to book that room at the same moment. A third insert for a different room, or for the same room at a non-overlapping time, succeeds normally because at least one of the two conditions (room_id equal, ranges overlapping) is false.

Partial exclusion: ignoring cancelled bookings

Real booking tables usually keep cancelled rows around for history and reporting rather than deleting them, but a cancelled booking should not block a new one for the same slot. Add a status column and scope the constraint with a WHERE clause, the same way you would create a partial index:

ALTER TABLE bookings ADD COLUMN status text NOT NULL DEFAULT 'confirmed';
 
ALTER TABLE bookings
  ADD CONSTRAINT bookings_no_overlap_active
  EXCLUDE USING gist (room_id WITH =, during WITH &&)
  WHERE (status <> 'cancelled');

Only rows that satisfy the WHERE predicate participate in the exclusion check, so a row updated to status = 'cancelled' drops out of consideration and a new booking can be made for the same room and time without touching the cancelled record. This is the same trick as a partial unique index, applied to an exclusion constraint instead.

Deferring the check to commit time

By default, an EXCLUDE constraint (like most constraints in Postgres) is checked immediately after each row-level operation within a transaction. That is usually what you want, but it can get in the way when a single transaction needs to pass through an intermediate state that looks invalid before settling into a final state that is fine — for example, swapping the time slots of two existing bookings. Updating the first booking to the second one's old time range might momentarily overlap with the second booking's still-unmodified row, even though the transaction will fix that up in its very next statement.

Declaring the constraint as DEFERRABLE INITIALLY DEFERRED postpones the check until COMMIT, so only the final state of the transaction has to satisfy it:

ALTER TABLE bookings
  ADD CONSTRAINT bookings_no_overlap_deferrable
  EXCLUDE USING gist (room_id WITH =, during WITH &&)
  DEFERRABLE INITIALLY DEFERRED;

Inside a transaction you can now update two overlapping-looking rows in either order, and Postgres only validates the constraint when the transaction commits, at which point both rows are already in their correct, non-conflicting final positions.

Inspecting and dropping the constraint

Exclusion constraints show up in pg_constraint with contype = 'x'. pg_get_constraintdef renders the human-readable definition, which is useful for confirming exactly which operators and WHERE clause are in effect without reverse-engineering the catalog columns yourself:

SELECT conname, pg_get_constraintdef(oid)
FROM   pg_constraint
WHERE  conrelid = 'bookings'::regclass
  AND  contype = 'x';

Dropping one is the same as dropping any other named constraint:

ALTER TABLE bookings DROP CONSTRAINT bookings_no_overlap;

EXCLUDE constraints versus application-level locking

The alternative to an EXCLUDE constraint is to keep the "check then insert" pattern from the beginning of this article, but wrap it in explicit locking so the check and the insert become atomic from the application's point of view — typically a SELECT ... FOR UPDATE against the rows for the same room, taken before the conflict check runs, so a second concurrent transaction blocks until the first one commits or rolls back. This can work, but it pushes real complexity onto every code path that writes to the table: every writer has to lock in a consistent order to avoid deadlocks, has to remember to take the lock at all (a single forgotten code path reintroduces the race condition), and has to hold that lock for the duration of a check-then-write sequence rather than a single statement.

An EXCLUDE constraint moves that responsibility into the schema itself. There is no lock ordering to get wrong, no code path that can forget to check, and the guarantee holds even if some other tool or a stray script inserts into the table directly. The trade-off is index maintenance cost: a GiST index is more expensive to update than a plain B-tree, so on a table with an extremely high insert rate the constraint adds measurable overhead compared to a bare UNIQUE index. For the vast majority of booking-style tables, where reads and moderate write volumes dominate, that cost is a reasonable price for correctness that does not depend on every caller getting locking right.

Wrapping up

EXCLUDE constraints turn overlap-prevention from an application-level convention into a database-enforced guarantee. Reach for tstzrange or daterange columns with the default [) bounds, install btree_gist when you need to mix an equality column like room_id into the same GiST index as a range column, and add a WHERE clause when historical rows such as cancellations should not participate in the check. If you would rather not memorize the exact EXCLUDE USING gist (... WITH =, ... WITH &&) syntax every time you set one of these up, the Postgres EXCLUDE Constraint Generator (opens in a new tab) builds the statement for you from your table and column names. And once the constraint is in place, a client like Chat2DB is a convenient way to browse pg_constraint, run test inserts against a staging table, and confirm the exact error text before shipping the migration.