A PostgreSQL exclusion constraint can reject two rows even when none of their stored values are equal. Its rule is pairwise: for any two candidate rows, the configured operator comparisons must not all evaluate to true. That makes the constraint suitable for invariants such as non-overlapping time intervals, where scalar uniqueness does not describe the forbidden state.

The mechanism is more general than a scheduling convenience. It turns an operator-defined notion of conflict into a database constraint, with an index access method participating in conflict detection. The exact operators, their null behavior, range boundaries, and any constraint predicate determine which row pairs are legal.

The invariant is a conjunction that must never stay true

A unique constraint rejects duplicate key values. An exclusion constraint instead declares one or more comparisons between row pairs:

EXCLUDE USING gist (
    room_id WITH =,
    occupied_at WITH &&
)

For two rows to conflict here, both comparisons must be true: their room_id values are equal and their occupied_at ranges overlap. If either comparison is false or null, that pair does not violate this exclusion rule.

This gives the declaration a precise logical shape. It does not state that each listed expression must be globally unique. It states that no pair of rows may make the complete set of listed operator tests true at once.

That distinction permits a room to have many reservations and permits the same time interval to appear for different rooms. The forbidden state is narrower: equal room identity combined with overlapping occupancy.

Range overlap is different from endpoint equality

PostgreSQL range types carry boundary semantics as part of the value. For a half-open timestamp range such as [09:00,10:00), the lower bound is included and the upper bound is excluded. A following range [10:00,11:00) does not overlap it.

That boundary choice changes the result of the range overlap operator &&, and the exclusion constraint follows that operator result. Two adjacent half-open reservations can therefore coexist:

INSERT INTO reservation (room_id, occupied_at)
VALUES
    (7, '[2026-09-16 09:00,2026-09-16 10:00)'::tsrange),
    (7, '[2026-09-16 10:00,2026-09-16 11:00)'::tsrange);

Changing a bound from exclusive to inclusive can change the conflict relation. A constraint on ranges is consequently tied to the range values’ boundary semantics, not merely to the visible endpoint timestamps.

This is one reason two separate scalar columns such as starts_at and ends_at do not automatically carry the same model. The overlap relation has to be defined somewhere. A range value plus its operator gives PostgreSQL a native representation of that relation.

Equality can scope a non-equality conflict

Many useful exclusion rules combine ordinary equality with a non-equality operator. The room reservation form is a representative case:

CREATE EXTENSION IF NOT EXISTS btree_gist;

CREATE TABLE reservation (
    reservation_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    room_id bigint NOT NULL,
    occupied_at tsrange NOT NULL,
    EXCLUDE USING gist (
        room_id WITH =,
        occupied_at WITH &&
    )
);

GiST has native support for range operations. The btree_gist extension supplies GiST operator classes for many scalar data types, allowing equality on a scalar identifier to participate in the same multicolumn GiST-backed exclusion constraint.

The equality term acts as a scope boundary. Rows for room 7 are compared for temporal overlap against other rows for room 7, while a row for room 8 can occupy the same interval without making every comparison true.

This structure also applies outside time. Any design depends on suitable data types, operators, and operator classes for the selected index access method. The constraint is only as meaningful as the conflict relation those operators encode.

A prior SELECT does not provide the same invariant

An application can query for an overlap before inserting:

SELECT 1
FROM reservation
WHERE room_id = $1
  AND occupied_at && $2::tsrange;

That query can be useful for presenting availability, but its empty result is not equivalent to a schema constraint. Another transaction can act after the query and before the insert. Treating the pre-check as the sole enforcement mechanism leaves correctness dependent on transaction coordination beyond the query itself.

The exclusion constraint moves the final admissibility decision into the write path. An application may still perform a pre-check for interface purposes, but the database constraint remains the authority on whether the row pair can coexist.

This separation also keeps the application from having to duplicate every detail of PostgreSQL range semantics in a second conflict detector.

Null results weaken a pairwise conflict test

The exclusion rule is violated only when all configured comparisons for a row pair return true. A false result breaks the conjunction, and so does a null result.

That matters when constrained expressions can be null. If room_id is nullable, equality against another row can evaluate to null rather than true. If the intended domain says every reservation belongs to exactly one room, NOT NULL is part of the invariant rather than a cosmetic addition.

The same principle applies to expressions and operators with other sources of null results. Exclusion syntax does not silently convert three-valued SQL logic into a two-valued conflict predicate.

A robust declaration therefore considers column nullability together with the exclusion operators. The pairwise rule and the domain constraints form one semantic unit.

Partial exclusion constraints narrow the participating row set

An exclusion constraint can include a predicate. Only rows satisfying that predicate participate in the associated partial index and exclusion rule.

For a model where cancelled reservations may overlap active ones, a declaration can scope enforcement:

EXCLUDE USING gist (
    room_id WITH =,
    occupied_at WITH &&
)
WHERE (status = 'active')

The resulting invariant is not “reservations never overlap.” It is “rows selected by this predicate cannot form the configured conflict.” A status transition can therefore become the operation that brings an existing row into the constrained set.

This is a useful boundary, but it also makes predicate semantics part of correctness. If several status values should block capacity, a predicate that names only one of them encodes a narrower invariant than the application may intend.

The index is part of enforcement, not a substitute declaration

PostgreSQL automatically creates an index for an exclusion constraint. The selected operators must be supported by suitable operator classes for the chosen access method, and the operators used by the exclusion declaration must be commutative.

The index is an implementation component of constraint enforcement and can also support relevant indexed operations. Still, an ordinary index declaration does not itself create the exclusion invariant. A GiST index on occupied_at can accelerate overlap searches while allowing overlapping rows to remain stored.

This distinction mirrors the difference between search structure and admissibility rule. Indexing can make a relation searchable; the exclusion constraint makes a configured relation illegal between stored row pairs.

Deferrability changes the check boundary, not the conflict relation

PostgreSQL exclusion constraints can be declared deferrable. As with other deferrable constraints, that property changes when enforcement can occur inside a transaction. It does not alter the operators that define a conflict.

A deferred exclusion rule can permit a transaction to pass temporarily through a conflicting intermediate state, provided the conflict is gone when the selected constraint check occurs. An immediate rule requires the relevant statement boundary to satisfy the constraint.

This timing choice is separate from the pairwise logic. The operator set answers which pairs conflict; deferrability answers when PostgreSQL requires those conflicts to be absent.

That separation matters for multi-step rearrangements of intervals. A transaction may need to move several ranges through temporary overlap even though its final state is valid. The schema can represent that timing requirement explicitly when deferral is appropriate.

Exclusion constraints place relational conflict in the schema

Some invariants are not properties of one row and are not reducible to scalar uniqueness. They describe forbidden relationships between pairs of rows: overlapping intervals within one resource, intersecting geometric objects within one scope, or another operator-defined collision.

PostgreSQL exclusion constraints provide a direct form for that class of rule. Their semantics remain exact: compare each row pair with the declared operators, and reject a state in which every comparison for a pair is true.

The practical consequence is a cleaner boundary between search and enforcement. Applications can query potential conflicts for presentation or planning, while the schema defines the final legal state using the same database-level operator semantics that characterize the conflict.

Software Engineering PostgreSQL Databases Constraints