In PostgreSQL 18, what do PRIMARY KEY ... WITHOUT OVERLAPS and FOREIGN KEY ... PERIOD enforce, and what do they still not support?
answer
- last key column is a range
- enforced like an EXCLUDE
- parents together cover the child
- one referential action left
- no FOR PORTION OF yet
basics
~20 sWITHOUT OVERLAPS makes a key's last, range-typed column unique by non-overlap, enforced like an EXCLUDE constraint. PERIOD makes a foreign key require parent rows whose periods together cover the child's period. Temporal foreign keys accept only NO ACTION.
solid answer
~50 sPostgreSQL 18 lets the **last** column of a `PRIMARY KEY` or `UNIQUE` be marked `WITHOUT OVERLAPS`. It must be a non-empty range or multirange; the other columns are compared for equality, so `PRIMARY KEY (room_id, valid_at WITHOUT OVERLAPS)` behaves like `EXCLUDE USING gist (room_id WITH =, valid_at WITH &&)`, uses a GiST index, needs `btree_gist` for a scalar `room_id`, and reports clashes as `23P01`, not `23505`. `FOREIGN KEY (room_id, PERIOD during) REFERENCES room_version (room_id, PERIOD valid_at)` then requires parent rows whose periods **together** cover the child's whole range, and its target must be a `WITHOUT OVERLAPS` key. Still missing in 18: `RESTRICT`, `CASCADE`, `SET NULL` and `SET DEFAULT` on temporal foreign keys, leaving only `NO ACTION`; `UPDATE`/`DELETE ... FOR PORTION OF`, so splitting a period is your SQL's job; and the key cannot arbitrate `ON CONFLICT DO UPDATE`.
code
sql · 11 linesINSERT INTO room_version (room_id, valid_at, capacity) VALUES
(7, tstzrange('2026-10-01 00:00+00', '2026-10-06 12:00+00'), 8),
(7, tstzrange('2026-10-06 12:00+00', '2027-01-01 00:00+00'), 12);
-- covered by the two versions combined: accepted
INSERT INTO booking (room_id, during)
VALUES (7, tstzrange('2026-10-06 11:00+00', '2026-10-06 13:00+00'));
-- room 7 has no version before 2026-10-01: rejected with 23503
INSERT INTO booking (room_id, during)
VALUES (7, tstzrange('2026-09-30 09:00+00', '2026-09-30 10:00+00'));go deeper
Recall that PostgreSQL 18 added WITHOUT OVERLAPS for the last column of a primary or unique key and PERIOD for foreign keys, both on range columns.
Explain that the temporal key is enforced like an EXCLUDE on a GiST index, needs btree_gist for scalar columns and raises 23P01, and what PERIOD coverage by combined parent rows means.
Show you know the gaps: only NO ACTION, no FOR PORTION OF, no DO UPDATE arbiter, no online creation, and how a deferred foreign key makes a period split possible.
Decide when declarative temporal keys earn their GiST cost and 18-only syntax versus a hand-written EXCLUDE plus triggers, given upgrade plans and the tools that must read the schema.
## The problem temporal keys solve In a meeting-room system, rooms change over time: room 7 seats 8 people until a refurbishment, then 12, and it is closed for two weeks in between. A common design keeps one row per **room version**, each with a validity period. Two rules matter: - a room may not have two versions whose periods overlap, or "what was room 7's capacity at 11:00?" has two answers; - a booking may reference room 7 only for a time when the room actually exists. Before PostgreSQL 18 the first rule needed a hand-written `EXCLUDE` constraint and the second needed triggers. PostgreSQL 18 makes both declarative. (Valid time versus system time, and whether to version a table at all, are temporal-modelling topics; this answer is about PostgreSQL's syntax and rules.) ## WITHOUT OVERLAPS: a temporal primary key ```sql CREATE EXTENSION IF NOT EXISTS btree_gist; CREATE TABLE room_version ( room_id bigint NOT NULL, valid_at tstzrange NOT NULL, capacity int NOT NULL, PRIMARY KEY (room_id, valid_at WITHOUT OVERLAPS) ); ``` The rules, from the 18 manual and source: - `WITHOUT OVERLAPS` applies only to the **last** column of the key, and the key needs at least one other column ("constraint using WITHOUT OVERLAPS needs at least two columns"). - That column must be a **range or multirange** type, and **empty** values are rejected. - The other columns are compared for equality inside a **GiST** index. By default only range types work there; for `bigint`, `uuid` or `text` you add **`btree_gist`**, which the manual calls the expected way to use the feature. - Enforcement is an exclusion check: the key behaves like `EXCLUDE USING gist (room_id WITH =, valid_at WITH &&)`. A clash raises **`23P01` exclusion_violation** ("conflicting key value violates exclusion constraint"), not `23505`. - As a primary key it also makes both columns `NOT NULL`. What a plain `EXCLUDE` cannot give you is the catalog status: this is a real primary key or unique constraint, so a foreign key can reference it. ## PERIOD: a temporal foreign key ```sql ALTER TABLE booking ADD CONSTRAINT booking_room_exists FOREIGN KEY (room_id, PERIOD during) REFERENCES room_version (room_id, PERIOD valid_at); ``` The semantics: 1. The non-`PERIOD` columns (at least one) match by equality, as in any foreign key. 2. The `PERIOD` column does not match by equality. The constraint holds when the parent rows with that `room_id` have periods whose **combined** extent covers the booking's entire range: the booking needs a referent for its whole duration. 3. Both sides must be marked `PERIOD`, and the referenced key must be declared `WITHOUT OVERLAPS`. If you omit the referenced column list, the parent's primary key must end in a `WITHOUT OVERLAPS` column. So with versions `[09:00, 12:00)` and `[12:00, 18:00)` for room 7, a booking for `[11:00, 13:00)` is valid although neither version covers it alone. Delete the second version and the booking is uncovered from 12:00, so the delete fails. ## What 18 still does not do | Gap | Consequence | |---|---| | Referential actions | `RESTRICT`, `CASCADE`, `SET NULL` and `SET DEFAULT` are rejected for a `PERIOD` foreign key ("unsupported ON DELETE action for foreign key constraint using PERIOD"); only the default `NO ACTION` works | | `FOR PORTION OF` | 18's `UPDATE` and `DELETE` cannot change just part of a period; splitting a version is several statements of your own | | Upserts | `ON CONFLICT DO UPDATE` cannot use the key as arbiter, because it is enforced as an exclusion constraint | | Online creation | the key cannot be added `NOT VALID`, and `ADD ... USING INDEX` accepts only a B-tree unique index | ## Splitting a period safely Because there is no `FOR PORTION OF`, a capacity change at 12:00 means shrinking the old version and inserting a new one. Order matters with a non-deferred foreign key, which is checked at the end of each statement: - shrink first, and bookings after 12:00 are uncovered when that `UPDATE` ends, so it fails; - insert first, and the new version overlaps the old one, so the temporal primary key raises `23P01`. The way out is a temporal foreign key declared `DEFERRABLE` (temporal foreign keys accept it) and deferred for the transaction, so coverage is checked once at `COMMIT`, after both statements.
- In PostgreSQL 18, how do you split a room version's period without breaking booking coverage?In one transaction, with the temporal foreign key deferred. Shrinking the old version first leaves later bookings uncovered at that statement's end; inserting the new version first overlaps the old one and raises `23P01`. Declare the foreign key `DEFERRABLE` (or switch it with `ALTER CONSTRAINT`), `SET CONSTRAINTS booking_room_exists DEFERRED`, shrink, insert, and coverage is checked at `COMMIT`.
- Why does PostgreSQL 18 raise 23P01, not 23505, for a clash in a WITHOUT OVERLAPS primary key?The key is enforced like an exclusion constraint: a GiST index on `room_id WITH =, valid_at WITH &&` and an exclusion check, not a B-tree uniqueness check. The error reads "conflicting key value violates exclusion constraint". Code that maps only `23505` to "already exists" misses these clashes, so the error mapping must include `23P01`.
- Can you add a WITHOUT OVERLAPS primary key to a large, busy PostgreSQL 18 table without blocking writes?Not online. `ADD PRIMARY KEY` builds the GiST index inside `ALTER TABLE` under `ACCESS EXCLUSIVE`, the key cannot be `NOT VALID`, and `ADD CONSTRAINT ... USING INDEX` accepts only a plain B-tree unique index, so a concurrently built GiST index cannot be attached. Plan a window or build a new table and swap; lock-level detail belongs to online schema changes.
- A WITHOUT OVERLAPS primary key raises 23505 unique_violation when periods overlap.
- WITHOUT OVERLAPS can be put on any column of the key, not only the last.
- A PERIOD foreign key needs one parent row whose period alone covers the child.
- Temporal foreign keys support ON DELETE CASCADE like ordinary foreign keys.
- PostgreSQL 17 already ships WITHOUT OVERLAPS keys and PERIOD foreign keys.
- A temporal key on an integer room_id works without btree_gist.
Assumes PostgreSQL 18, where WITHOUT OVERLAPS keys and PERIOD foreign keys first shipped; on 17 and earlier you hand-write the EXCLUDE constraint and check coverage in triggers.