PostgreSQL 18 Temporal Constraints: WITHOUT OVERLAPS and PERIOD

PostgreSQL 18 adds temporal constraints for range and multirange columns. Use WITHOUT OVERLAPS to stop rows with the same key from having overlapping periods, and use PERIOD in a foreign key to require that a referenced period is covered by the matching rows in another table.

These constraints are useful for reservations, memberships, rates, and other data whose validity changes over time. They require PostgreSQL 18 or later and a range or multirange type; see the range type guide for the available types.

Prevent overlapping periods with WITHOUT OVERLAPS

You can add WITHOUT OVERLAPS to the last column of a PRIMARY KEY or UNIQUE constraint. The column must be a range or multirange, and empty ranges are not allowed. PostgreSQL enforces the constraint with a GiST index.

When the key includes an ordinary scalar column such as room_id, enable btree_gist so GiST can compare that column:

CREATE EXTENSION btree_gist;

CREATE TABLE room_reservations (
    room_id integer NOT NULL,
    reserved_during tstzrange NOT NULL,
    customer_name text NOT NULL,
    PRIMARY KEY (room_id, reserved_during WITHOUT OVERLAPS)
);

The primary key allows two reservations for the same room when their ranges do not overlap. With half-open ranges, a reservation ending at 11:00 and another starting at 11:00 are adjacent, not overlapping:

INSERT INTO room_reservations (room_id, reserved_during, customer_name)
VALUES
    (101, tstzrange('2026-10-01 10:00+00', '2026-10-01 11:00+00', '[)'), 'Ada'),
    (101, tstzrange('2026-10-01 11:00+00', '2026-10-01 12:00+00', '[)'), 'Lin');

An overlapping reservation for room 101 is rejected:

INSERT INTO room_reservations (room_id, reserved_during, customer_name)
VALUES
    (101, tstzrange('2026-10-01 10:30+00', '2026-10-01 11:30+00', '[)'), 'Noah');

For a temporal primary key, every key column is also NOT NULL. If the range alone should be unique across the whole table, you can define PRIMARY KEY (reserved_during WITHOUT OVERLAPS) without a scalar key or btree_gist requirement.

Require full period coverage with a PERIOD foreign key

A temporal foreign key checks that matching rows in the referenced table cover the entire referencing period. The non-period columns are matched by equality; the period columns must both be range or multirange types. The referenced table needs a PRIMARY KEY or UNIQUE constraint whose final column uses WITHOUT OVERLAPS.

CREATE TABLE service_periods (
    account_id integer NOT NULL,
    valid_during daterange NOT NULL,
    plan_name text NOT NULL,
    PRIMARY KEY (account_id, valid_during WITHOUT OVERLAPS)
);

CREATE TABLE charges (
    charge_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    account_id integer NOT NULL,
    billed_during daterange NOT NULL,
    FOREIGN KEY (account_id, PERIOD billed_during)
        REFERENCES service_periods (account_id, PERIOD valid_during)
);

The account below has two adjacent plan periods. A charge period that spans both is valid because their combined ranges cover it completely:

INSERT INTO service_periods (account_id, valid_during, plan_name)
VALUES
    (42, daterange(DATE '2026-01-01', DATE '2026-06-01', '[)'), 'Basic'),
    (42, daterange(DATE '2026-06-01', DATE '2027-01-01', '[)'), 'Pro');

INSERT INTO charges (account_id, billed_during)
VALUES (42, daterange(DATE '2026-05-01', DATE '2026-07-01', '[)'));

If the referencing period includes a gap not covered by any matching parent periods, the foreign key rejects the row. Temporal foreign keys do not support RESTRICT, CASCADE, SET NULL, or SET DEFAULT referential actions. Omit these clauses and use the default NO ACTION behavior.

When to use these constraints

• Use WITHOUT OVERLAPS when a resource, account, or key must not have overlapping validity ranges.
• Use PERIOD when a child row must be covered by one or more matching parent periods.
• Use a regular UNIQUE or foreign key constraint when the rule concerns point values rather than time ranges.

For related material, see the PostgreSQL UNIQUE constraint guide and the foreign key tutorial. The PostgreSQL 18 CREATE TABLE documentation defines all restrictions for these constraints; see also the btree_gist extension documentation.