Skip to content
Tutorials Center
My learning

Topics

Lesson 34 of 90 ยท Schema Design and Constraints

Exclusion Constraints and Temporal Design

Stop double bookings in PostgreSQL with EXCLUDE USING gist and range types, then see PostgreSQL 18 temporal keys with WITHOUT OVERLAPS and PERIOD.

A UNIQUE constraint says "no two rows may be equal". Some rules are not about equality. A meeting room cannot be booked twice at the same time. A price list cannot have two prices for the same product on the same day. An employee cannot have two salaries in the same month. In each case, two rows are in conflict when their time ranges overlap, and UNIQUE cannot express that.

PostgreSQL has a constraint type made for this: the exclusion constraint. It generalizes uniqueness to any operator, including "overlaps". PostgreSQL 18 adds temporal keys on top of it, which make the most common case shorter. This lesson explains both, and shows how to find overlaps with a plain query that works in any database.

What runs live here. Exclusion constraints, range types and the btree_gist extension are PostgreSQL features, so those blocks are read-only. The live editors on this page run SQLite and use a standard overlap query on start and end columns. That query is the same logic the constraint enforces, so you can test your data and your reasoning.

Why UNIQUE is not enough

Bookings Two rows overlap in time, but are not equal.
Price history One price per product per day.
Rooms Same room, same night, one guest.
1 / 2

The overlap test

Before looking at the constraint, you need the rule it enforces. Two time intervals overlap when each one starts before the other ends. With a start and an end column, that is:

TEXT
a.start < b.end  AND  b.start < a.end

This test uses < and not <=, which assumes half-open intervals: the start is included and the end is excluded. A booking from 10:00 to 11:00 and another from 11:00 to 12:00 touch at 11:00 but do not overlap. PostgreSQL range types use half-open bounds by default for the same reason ([10:00, 11:00)).

This query finds every pair of conflicting bookings in a table:

SQL Find overlapping bookings

Press Run (or Ctrl + Enter) to see the result here.

The result has two conflicts: bookings 2 and 3 in the Oak room, and bookings 4 and 5 in the Pine room. Bookings 1 and 2 touch at 10:00 and are not reported, because the end of one equals the start of the other. a.id < b.id prevents each pair from being listed twice and a booking from being compared with itself.

Checking before inserting

The same logic can guard an insert. This query asks "is the slot free?" and returns zero rows when it is:

SQL Is this slot free?

Press Run (or Ctrl + Enter) to see the result here.

The first request has 1 conflict and the second has 0. This works as a pre-check, but it has a flaw on a busy system. Two sessions can run the check at the same moment, both see an empty slot, and both insert. Between "check" and "insert" there is a gap. Closing that gap in code needs locks or serializable transactions. An exclusion constraint closes it inside the database.

EXCLUDE USING gist

An exclusion constraint says: for any two rows, not all of these comparisons may be true at once. A unique constraint is the special case where every comparison is =. For bookings, the rule is "same room and overlapping time":

SQL
CREATE EXTENSION IF NOT EXISTS btree_gist;

CREATE TABLE bookings (
  id     bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  room   text NOT NULL,
  during tstzrange NOT NULL,
  EXCLUDE USING gist (
    room   WITH =,
    during WITH &&
  )
);

INSERT INTO bookings (room, during)
VALUES ('Oak', '[2025-06-01 09:00+00, 2025-06-01 10:00+00)');

INSERT INTO bookings (room, during)
VALUES ('Oak', '[2025-06-01 09:30+00, 2025-06-01 10:30+00)');
-- ERROR:  conflicting key value violates exclusion constraint "bookings_room_during_excl"

Here is what each piece does:

  • tstzrange is a range of timestamptz values. '[a, b)' includes a and excludes b. See Range and Multirange Types.
  • && is the "overlaps" operator for ranges. room WITH = means the rooms are equal.
  • USING gist selects the index type that supports these operators. The constraint is backed by a GiST index, which is also what makes the check fast.
  • btree_gist is a contrib extension that teaches GiST to handle plain equality on ordinary types such as text and integer. You need it as soon as you mix = on a scalar with && on a range.

The second insert fails with the error code 23P01 (exclusion_violation). Because the check runs inside the index at write time, two concurrent transactions cannot both succeed. One waits for the other and then fails if the rows conflict.

Some more practical points:

  • Different rooms never conflict, because room WITH = must be true for the pair to be excluded.
  • You can add a condition to limit the rule to some rows, for example WHERE (status <> 'cancelled'), so cancelled bookings no longer block a slot.
  • Exclusion constraints can be DEFERRABLE, like unique constraints (see Constraints in Depth). You cannot use them as the target of a foreign key.
  • Without the range type, you can still use two columns by indexing an expression such as tstzrange(starts_at, ends_at).

PostgreSQL 18: WITHOUT OVERLAPS

One common exclusion constraint is so frequent that PostgreSQL 18 gives it dedicated syntax. A temporal key is a primary key or unique constraint whose last column is a range and is marked WITHOUT OVERLAPS:

SQL
CREATE TABLE product_prices (
  product_id bigint NOT NULL,
  valid_at   daterange NOT NULL,
  price      numeric(10,2) NOT NULL,
  PRIMARY KEY (product_id, valid_at WITHOUT OVERLAPS)
);

INSERT INTO product_prices VALUES
  (1, '[2025-01-01, 2025-07-01)', 9.99),
  (1, '[2025-07-01, 2026-01-01)', 11.99);  -- touches, so it is allowed

INSERT INTO product_prices VALUES
  (1, '[2025-06-15, 2025-08-01)', 10.49);  -- overlaps both rows: error

This reads as "each product has at most one price at any moment". It behaves like an exclusion constraint on product_id WITH =, valid_at WITH &&, but it is also a real primary key, so the columns are NOT NULL and other tables can reference it. It still needs btree_gist for the scalar column.

Temporal keys come with matching foreign keys using PERIOD. A child row's range must be fully covered by the combined ranges of the parent rows:

SQL
CREATE TABLE price_overrides (
  product_id bigint NOT NULL,
  valid_at   daterange NOT NULL,
  note       text,
  FOREIGN KEY (product_id, PERIOD valid_at)
    REFERENCES product_prices (product_id, PERIOD valid_at)
);

An override from 2025-06-01 to 2025-08-01 is accepted because the two price rows together cover those dates, even though neither covers it alone. If you are on PostgreSQL 17 or earlier, use the explicit EXCLUDE USING gist form instead.

Designing temporal tables

A few habits keep temporal data healthy:

  1. Use half-open ranges ([start, end)) everywhere. Adjacent periods then fit together without gaps or double counting.
  2. Store "no end" as an unbounded range, such as [2025-01-01,), rather than a fake far-future date.
  3. Never update history in place. To change a price from July, close the old row and insert a new one, so you can still ask what the price was in June.
  4. Name the column for its meaning: valid_at for when a fact is true in the real world, recorded_at for when you stored it. Keeping both is called bitemporal design.
  5. Add the constraint at creation time. Cleaning up overlapping rows later is painful, and the constraint refuses to build if existing rows already conflict.

The query from the start of this lesson remains useful after the constraint exists: it is how you audit imported data before loading it into a constrained table.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. Bookings A (10:00 to 11:00) and B (11:00 to 12:00) are stored as half-open ranges. Do they overlap?

    With half-open bounds the end instant is not part of the range, so back-to-back periods never overlap. That is why the overlap test uses strict < comparisons.
  2. 2. Why can an application-level check before INSERT still allow a double booking?

    There is a gap between checking and inserting. A constraint is enforced at write time inside the database, so concurrent writers cannot both succeed.
  3. 3. Which extension lets a GiST exclusion constraint use = on an ordinary text column together with && on a range?

    btree_gist adds GiST operator support for plain scalar types, which is needed to combine equality on room with overlap on a range.
  4. 4. What does PRIMARY KEY (product_id, valid_at WITHOUT OVERLAPS) guarantee in PostgreSQL 18?

    It combines equality on product_id with non-overlap on the range, so each product has at most one row at any moment. Many rows per product are fine as long as the ranges do not overlap.
  5. 5. Which SQLSTATE class of error does a violated exclusion constraint raise?

    Exclusion constraints have their own error code, 23P01. Applications that catch it can tell the user the slot is already taken.

Summary

  • Two time ranges conflict when a.start < b.end AND b.start < a.end. Use half-open intervals so adjacent periods do not overlap.
  • A plain check-then-insert is racy; an EXCLUDE USING gist constraint enforces the rule inside the database.
  • Combine room WITH = and during WITH &&; the btree_gist extension is needed for equality on scalar columns.
  • PostgreSQL 18 adds WITHOUT OVERLAPS for primary and unique keys, and PERIOD foreign keys for range coverage.
  • Keep history by closing rows and inserting new ones, not by updating in place.
  • The live editors ran the overlap logic on SQLite; exclusion and temporal syntax are PostgreSQL features shown read-only.

Next: Views and Materialized Views.