A unique constraint can reject two equal scalar values, but equality is too narrow for many scheduling and allocation rules. Two reservations can have different start and end timestamps while still occupying the same interval. PostgreSQL exclusion constraints express this kind of conflict directly in the database.

An exclusion constraint compares pairs of rows with declared operators. A pair is rejected when every operator comparison in the constraint is true. This turns operators such as range overlap into enforceable cross-row rules without relying on a query followed by an insert.

Range overlap is an operator

PostgreSQL range types represent an interval as one value. A tstzrange, for example, can store a timestamp interval with explicit lower and upper bound semantics. The && operator tests whether two ranges overlap.

A table that must reject overlapping reservations can attach that operator to an exclusion constraint:

CREATE TABLE reservations (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    occupied_at tstzrange NOT NULL,
    EXCLUDE USING gist (occupied_at WITH &&)
);

For any candidate row, PostgreSQL checks existing rows through the constraint’s backing index. If an existing occupied_at value overlaps the candidate value, && is true and the insert or update conflicts with the constraint.

This differs from UNIQUE (occupied_at). Uniqueness rejects identical range values, while the exclusion rule rejects distinct ranges that intersect. The operator defines the prohibited relationship.

Multiple operators narrow the conflict condition

Many interval rules are scoped to a resource. Two rooms may be occupied at the same time, while one room must not have overlapping reservations. That rule needs equality on the resource plus overlap on the interval:

CREATE EXTENSION btree_gist;

CREATE TABLE room_reservations (
    room_id bigint NOT NULL,
    occupied_at tstzrange NOT NULL,
    EXCLUDE USING gist (
        room_id WITH =,
        occupied_at WITH &&
    )
);

The pair of rows conflicts only when both comparisons are true. Different room_id values make the equality comparison false, so overlapping time ranges are permitted across different rooms. Equal room identifiers combined with overlapping ranges make every declared comparison true, so the second row is rejected.

The btree_gist extension supplies GiST operator classes with B-tree-like behavior for common scalar types. That allows a scalar equality condition to share a multicolumn GiST index with a range operator supported by GiST.

Bound semantics are part of the rule

Range values carry inclusive or exclusive bounds. The common half-open form [start, end) includes the lower bound and excludes the upper bound. Adjacent reservations can therefore meet at one timestamp without overlapping:

[09:00, 10:00)
              [10:00, 11:00)

The first range does not contain 10:00, while the second begins there. The overlap operator follows the range type’s bound semantics, so the exclusion constraint follows them as well.

This is a useful property of storing an interval as a range rather than maintaining independent starts_at and ends_at columns plus custom comparison logic. The database has one value with defined containment, adjacency, and overlap behavior.

Empty ranges also deserve attention. An empty range contains no points and does not overlap a nonempty range. If the application domain treats zero-duration allocations as invalid, that rule should be expressed separately rather than assumed from the exclusion constraint.

The index is part of enforcement

Adding an exclusion constraint creates an index using the access method named in the declaration. GiST is a common choice because its operator classes support range relationships such as overlap.

This index is not merely an optional performance aid for a validation query. It is part of how PostgreSQL enforces the constraint. Each constrained operator must belong to an operator class suitable for the selected index access method, and the operators used by an exclusion constraint must be commutative.

The resulting index may also be usable by ordinary queries whose predicates match supported operators. Its primary reason for existing, however, is constraint enforcement. Index design for unrelated query patterns should still be evaluated independently.

Partial exclusion rules can target active rows

Exclusion constraints accept a predicate, producing a partial backing index. This can model a conflict rule that applies only to a subset of rows. A reservation system that retains cancelled entries, for example, can exclude overlap only among active records:

CREATE TABLE bookings (
    resource_id bigint NOT NULL,
    occupied_at tstzrange NOT NULL,
    cancelled_at timestamptz,
    EXCLUDE USING gist (
        resource_id WITH =,
        occupied_at WITH &&
    ) WHERE (cancelled_at IS NULL)
);

A cancelled row falls outside the predicate and no longer participates in this exclusion rule. The predicate is therefore part of the integrity model, not just an index-size optimization.

State transitions need the same scrutiny as inserts. Changing cancelled_at from a timestamp to NULL brings a row back into the constrained set, so PostgreSQL checks it against other qualifying rows during the update.

Constraint checks avoid an application race

A separate application query such as SELECT ... WHERE occupied_at && $1 can detect an existing conflict, but a check followed by an insert is not itself a cross-transaction integrity guarantee. Another transaction can act between those statements.

An exclusion constraint moves the invariant into PostgreSQL’s constraint machinery. Concurrent changes that could create a prohibited pair are coordinated as part of enforcement rather than left to the timing of application-side reads. The application still needs to handle a constraint violation, but it no longer has to treat a prior availability query as proof that a later write cannot conflict.

Exclusion constraints are most useful when the invalid state can be described as a relationship between two rows using index-supported operators. Range overlap fits that model directly. Rules that depend on aggregates, arbitrary sets of rows, or external state do not become exclusion constraints merely because they involve conflicts.

The central design choice is the operator relationship that must never hold for a pair of qualifying rows. Once that relationship maps cleanly to PostgreSQL operators and an appropriate index access method, the database can enforce a richer invariant than scalar uniqueness without turning interval consistency into application timing logic.

PostgreSQL Database Constraints GiST