Applications that schedule rooms, equipment, or maintenance windows often need a simple invariant: two active reservations for the same resource must not overlap. Checking for conflicts in application code looks easy, but concurrent transactions can both pass the check before either inserts.

PostgreSQL can enforce this invariant inside the database with range types and exclusion constraints.

Model the interval explicitly

A half-open timestamp range includes its start and excludes its end. That lets adjacent bookings touch without overlapping.

CREATE TABLE reservation (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    room_id bigint NOT NULL,
    during tstzrange NOT NULL,
    CHECK (NOT isempty(during))
);

A reservation from 10:00 to 11:00 can coexist with one starting exactly at 11:00 when both use [) bounds.

Add an exclusion constraint

To combine equality on room_id with range overlap checks, enable the btree_gist extension in databases where it is permitted:

CREATE EXTENSION IF NOT EXISTS btree_gist;

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

The && range operator means “overlaps.” PostgreSQL rejects rows for which all listed operator comparisons are true against an existing row. The database therefore arbitrates concurrent inserts rather than trusting a prior application query.

Insert normalized ranges

INSERT INTO reservation (room_id, during)
VALUES (
    42,
    tstzrange(
        '2026-09-10 10:00:00+00',
        '2026-09-10 11:00:00+00',
        '[)'
    )
);

Use tstzrange when instants represent real points in time across time zones. Convert user-facing local times to validated instants before storage.

Handle constraint violations as normal conflicts

Two users can still submit competing reservations. One transaction should succeed and the other should receive a constraint violation. Treat that as an expected concurrency outcome and return an appropriate domain response, such as a conflict, rather than a generic server error.

Do not parse human-readable database error text. Use the driver’s structured error information and, when available, the constraint name.

Trade-offs

Exclusion constraints are PostgreSQL-specific and require understanding GiST indexes and range operators. They are less portable than application-only validation.

In exchange, they place the invariant next to the data and close a race that is otherwise easy to reintroduce. Application checks can still provide friendly early feedback, but the database constraint remains authoritative.

Common pitfalls

Using inclusive end points

Closed intervals make adjacent reservations overlap at their boundary. Choose range bounds intentionally and use them consistently.

Allowing empty or unbounded ranges accidentally

Validate whether empty or infinite intervals make sense for the domain. A scheduling system usually wants finite, non-empty periods.

Assuming a SELECT prevents races

A conflict query followed by an insert is not atomic under ordinary concurrency. Keep the constraint even if the UI performs a pre-check.

Forgetting cancellation semantics

If reservations can be canceled while history is retained in the same table, a plain constraint still sees canceled rows. Model active reservations separately or use a constraint design that matches the lifecycle.

Put invariants where concurrency is decided

When correctness depends on simultaneous writes, database constraints are stronger than best-effort application checks. PostgreSQL exclusion constraints are particularly well suited to “same key, non-overlapping range” rules and make scheduling behavior deterministic under contention.

Database PostgreSQL Constraints SQL