Temporal constraints vs exclusion constraints in Postgres 18

Quick answer: A temporal key is enforced by the same mechanism and is also a key. Measured on 2026-09-19 on PostgreSQL 18.6 in a stock postgres:18.6 container, the index behind UNIQUE (sku, valid_during WITHOUT OVERLAPS) is flagged both unique and exclusion in the catalog. Being a key is what lets a PERIOD foreign key name it, and temporal foreign keys are the new capability. FOR PORTION OF did not land in 18.

What is the problem?

You have rows with a validity period: a room booking, a price that applied for a quarter, an employment record. Two rules keep coming up. No two rows for the same room may overlap in time. A child row must exist only while its parent does.

The first has had an answer since PostgreSQL 9.0: the exclusion constraint. The second had none, so people built it with triggers.

PostgreSQL 18 added syntax for both rules. The syntax follows the SQL standard, but uses PostgreSQL’s range and multirange types rather than standard period columns. That distinction matters when porting a schema because the data model is not the standard’s.

The old way, which still works

An exclusion constraint says that no two rows may both satisfy the specified operators.

CREATE EXTENSION IF NOT EXISTS btree_gist;

CREATE TABLE IF NOT EXISTS old_bookings (
  id      int GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  room_id int NOT NULL,
  during  daterange NOT NULL,
  EXCLUDE USING gist (room_id WITH =, during WITH &&)
);

Two rows with the same room_id whose during values overlap cannot both exist. Measured on 2026-09-19 on PostgreSQL 18.6 in a stock postgres:18.6 container, that behaves exactly as it did on 17.

GiST is the usual access method here, but it is not the only one. Measured on 2026-09-19 on PostgreSQL 18.6 in a stock postgres:18.6 container, EXCLUDE USING spgist (during WITH &&) is also accepted, so an exclusion constraint is not a GiST constraint by definition.

When you need btree_gist, and when you do not

btree_gist is needed here because room_id is an integer, and PostgreSQL does not ship a default GiST operator class for integer in core. GiST can index it; the operator class lives in a contrib module.

The PostgreSQL 18 CREATE TABLE documentation puts the rule precisely: “The non-WITHOUT OVERLAPS columns of the constraint can be any type that can be compared for equality in a GiST index. By default, only range types are supported, but you can use other types by adding the btree_gist extension (which is the expected way to use this feature).”

Measured on 2026-09-19 on PostgreSQL 18.6 in a stock postgres:18.6 container with no extensions installed, that is what the catalog says. Asking which types have a default GiST operator class whose family contains an equality operator returns two rows, anyrange and anymultirange, and nothing else. The practical rule is: if the non-period key column is a range or multirange, no extension is needed; anything else, including int, text and uuid, needs btree_gist.

The PostgreSQL 18 btree_gist documentation also says the module “is considered ‘trusted’, that is, it can be installed by non-superusers who have CREATE privilege on the current database”. That is the database-level activation. The server package or image still has to ship the control and SQL files, which on many Linux distributions means a versioned contrib package, and some managed services restrict which extensions are available. Check availability before you write the migration.

If you omit it, the error names the type rather than the extension. Measured on 2026-09-19 on PostgreSQL 18.6 in a stock postgres:18.6 container without it:

ERROR:  data type integer has no default operator class for access method "gist"
HINT:  You must specify an operator class for the index or define a default
       operator class for the data type.

What an exclusion constraint cannot be

It is not a unique constraint, so an ordinary foreign key cannot reference it. Measured on 2026-09-19 on PostgreSQL 18.6 in a stock postgres:18.6 container:

FOREIGN KEY (room_id, during) REFERENCES old_bookings (room_id, during)

ERROR:  there is no unique constraint matching given keys for referenced table
        "old_bookings"

The new syntax removes that limit.

The new way

The same extension supports the new syntax:

CREATE TABLE IF NOT EXISTS bookings (
  room_id int NOT NULL,
  during  daterange NOT NULL,
  guest   text NOT NULL,
  CONSTRAINT bookings_pk PRIMARY KEY (room_id, during WITHOUT OVERLAPS)
);

Measured on 2026-09-19 on PostgreSQL 18.6 in a stock postgres:18.6 container, that succeeded. On PostgreSQL 17.11, measured on 2026-09-19 in a stock postgres:17.11 container, the same CREATE TABLE returns ERROR: syntax error at or near "WITHOUT", so the syntax is available only in 18 and later.

On that PostgreSQL 18.6 postgres:18.6 container on 2026-09-19:

INSERT INTO bookings VALUES (1, daterange '[2026-01-01,2026-02-01)', 'ada');
INSERT INTO bookings VALUES (1, daterange '[2026-02-01,2026-03-01)', 'grace');

Both were accepted. The ranges are adjacent, and [2026-01-01,2026-02-01) does not include the first of February, so they do not overlap.

An overlapping third row is refused:

INSERT INTO bookings VALUES (1, daterange '[2026-01-15,2026-02-15)', 'alan');

ERROR:  conflicting key value violates exclusion constraint "bookings_pk"
DETAIL:  Key (room_id, during)=(1, [2026-01-15,2026-02-15)) conflicts with
         existing key (room_id, during)=(1, [2026-01-01,2026-02-01)).

Read the error message. It says exclusion constraint, even though the constraint was declared as a primary key.

It is both, and the catalog proves it

One query shows it. Measured on 2026-09-19 on PostgreSQL 18.6 in a stock postgres:18.6 container, joining pg_index to pg_class for the backing indexes:

index	access method	indisunique	indisexclusion
old_bookings_room_id_during_excl (plain EXCLUDE)	gist	f	t
pricing_sku_valid_during_key (UNIQUE ... WITHOUT OVERLAPS)	gist	t	t
pricing_pkey (ordinary surrogate key)	btree	t	f

A temporal key is both an exclusion constraint and a unique index, and the catalog records it in two boolean columns.

The PostgreSQL 18 CREATE TABLE documentation describes the enforcement the same way: “In effect, such a constraint is enforced with an EXCLUDE constraint rather than a UNIQUE constraint. So for example UNIQUE (id, valid_at WITHOUT OVERLAPS) behaves like EXCLUDE USING GIST (id WITH =, valid_at WITH &&).”

Calling a temporal key an exclusion constraint with different syntax misses that it remains a key. It behaves like an exclusion constraint for enforcement and remains a key for metadata, for \d, and for being referenced.

It works on UNIQUE too, not only PRIMARY KEY

The temporal key does not have to be the table’s primary key. This matters when the temporal business identifier is an alternate key and you also want a surrogate primary key:

CREATE TABLE IF NOT EXISTS pricing (
  pricing_id   bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  sku          text NOT NULL,
  valid_during daterange NOT NULL,
  price        numeric NOT NULL,
  UNIQUE (sku, valid_during WITHOUT OVERLAPS)
);

Measured on 2026-09-19 on PostgreSQL 18.6 in a stock postgres:18.6 container, a PERIOD foreign key referencing that UNIQUE key was created and enforced: a child note inside the January price was accepted, and one running to March was rejected.

The syntax rules, each one measured

All measured on 2026-09-19 on PostgreSQL 18.6 in a stock postgres:18.6 container, all of them errors at CREATE TABLE time rather than surprises later:

what you write	what happens
PRIMARY KEY (during WITHOUT OVERLAPS, room_id)	ERROR: syntax error at or near ",". It must be the last column
PRIMARY KEY (during WITHOUT OVERLAPS) alone	ERROR: constraint using WITHOUT OVERLAPS needs at least two columns
during int as the period column	ERROR: column "during" in WITHOUT OVERLAPS is not a range or multirange type
during datemultirange	accepted. Multiranges work
inserting daterange 'empty'	ERROR: empty WITHOUT OVERLAPS value found in column "during" in relation "bookings"
UNIQUE NULLS NOT DISTINCT (sku, during WITHOUT OVERLAPS)	accepted. Ordinary null handling applies to a temporal UNIQUE

A primary key makes its columns NOT NULL, so the period column of a temporal PRIMARY KEY cannot be null. On a temporal UNIQUE it can, with the usual unique-null behaviour unless you write NULLS NOT DISTINCT.

Unbounded ranges are a design decision, not a default

An unbounded range is a valid value and blocks the whole open side. Measured on 2026-09-19 on PostgreSQL 18.6 in a stock postgres:18.6 container, after inserting [2026-01-01,) for room 9, a later booking at [2030-01-01,2030-02-01) for the same room was refused as a conflict.

That is usually what you want for a “current” row, but it is wrong for backfills and corrections, which then have to close the open period first. Decide it deliberately.

Half-open ranges, with the one detail people trip on

[) is the right convention for a continuous effective-dated history because the end of one period can equal the start of the next without overlapping. It is a convention rather than a rule: adjacent periods coexist whenever the stored ranges do not overlap.

The detail is that daterange canonicalizes and tsrange does not. Measured on 2026-09-19 on PostgreSQL 18.6 in a stock postgres:18.6 container:

SELECT daterange '[2026-01-01,2026-01-31]';   -->  [2026-01-01,2026-02-01)
SELECT tsrange   '[2026-01-01,2026-01-31]';   -->  ["2026-01-01 00:00:00",
                                                    "2026-01-31 00:00:00"]

A daterange written with [] is stored as [), so the bug never appears. With tsrange, the bounds are kept as written, and [] really does make adjacent periods overlap at one instant. If you use a timestamp range, write [) and mean it.

What is actually new: temporal foreign keys

CREATE TABLE IF NOT EXISTS charges (
  charge_id int GENERATED ALWAYS AS IDENTITY,
  room_id   int NOT NULL,
  during    daterange NOT NULL,
  amount    numeric NOT NULL,
  PRIMARY KEY (charge_id),
  CONSTRAINT charges_room_fk FOREIGN KEY (room_id, PERIOD during)
    REFERENCES bookings (room_id, PERIOD during)
);

PERIOD during is the temporal part. The rule is stronger than an ordinary foreign key: the ordinary key columns match by equality first, and then the periods of the matching parent rows, taken together, must cover the child row’s period completely. Intersecting one of them is not enough.

Measured on 2026-09-19 on PostgreSQL 18.6 in a stock postgres:18.6 container, with the two adjacent January and February bookings above in place:

child row	result
room 1, [2026-01-05,2026-01-10)	accepted, inside the January booking
room 1, [2026-01-20,2026-02-10)	accepted, spanned by the two adjacent bookings
room 2, [2026-01-05,2026-01-10)	rejected, no such room
room 1, [2026-01-05,2026-04-10)	rejected, extends past every booking

The last two both reported the same error. The DETAIL line is useful because it prints the period it could not cover:

ERROR:  insert or update on table "charges" violates foreign key
        constraint "charges_room_fk"
DETAIL:  Key (room_id, during)=(1, [2026-01-05,2026-04-10)) is not present
         in table "bookings".

“Not present” has an unusual meaning there. The room exists and January is booked; what is absent is complete cover for that period. The message is the same one an ordinary foreign key gives, so it does not tell you that the temporal part is what failed.

The row spanned by two adjacent bookings is a case that hand-written triggers often get wrong. A natural implementation looks for one parent row containing the child period, but there is no such row.

Only NO ACTION, and that is a design constraint

This restriction can force a redesign, and it is easy to miss because it only shows up when you write the action.

Measured on 2026-09-19 on PostgreSQL 18.6 in a stock postgres:18.6 container, ON DELETE CASCADE, ON DELETE SET NULL and ON DELETE RESTRICT were all refused with ERROR: unsupported ON DELETE action for foreign key constraint using PERIOD, and ON UPDATE SET DEFAULT with the same message naming ON UPDATE. NO ACTION was accepted. RESTRICT being on that list is unexpected because it is the obvious fallback when CASCADE is unavailable.

The PostgreSQL 18 CREATE TABLE documentation says the same of each of those actions, in the same words: “In a temporal foreign key, this option is not supported.”

So shortening or removing a parent booking is your problem, not the database’s. The lifecycle has to be an explicit application or procedure step: close the child periods, split them, or refuse the parent change. Deferring the foreign key check does not help either because the referenced key cannot be deferrable. Measured on 2026-09-19 on PostgreSQL 18.6 in a stock postgres:18.6 container, a PRIMARY KEY (room_id, during WITHOUT OVERLAPS) DEFERRABLE was created, and the PERIOD foreign key to it was then refused with ERROR: cannot use a deferrable unique constraint for referenced table.

One measured behaviour that contradicts the documentation

The PostgreSQL 18 CREATE TABLE documentation is explicit about what a temporal foreign key may reference: “the referenced table must have a primary key or unique constraint declared with WITHOUT OVERLAPS.”

Measured on 2026-09-19 on PostgreSQL 18.6 in a stock postgres:18.6 container, it accepted less than that. Here is the reproduction:

CREATE TABLE old_bookings (
  id      bigserial PRIMARY KEY,
  room_id int NOT NULL,
  during  daterange NOT NULL,
  EXCLUDE USING gist (room_id WITH =, during WITH &&)
);

CREATE TABLE child_bookings (
  id      bigserial PRIMARY KEY,
  room_id int NOT NULL,
  during  daterange NOT NULL,
  FOREIGN KEY (room_id, PERIOD during) REFERENCES old_bookings (room_id, PERIOD during)
);

old_bookings has no WITHOUT OVERLAPS constraint anywhere on it, only the plain EXCLUDE USING gist from the top of this page and an unrelated surrogate primary key. Both statements succeeded on 2026-09-19 on that PostgreSQL 18.6 postgres:18.6 container, and the constraint was then enforced correctly against two adjacent parent rows covering January and February 2026: the child inside one parent was accepted, the child spanning both adjacent parents was accepted, the child running past the end was rejected with Key (room_id, during)=(1, [2026-02-20,2026-03-10)) is not present in table "old_bookings", and the child with no parent was rejected the same way.

What backs it is the exclusion constraint’s own index, on that same PostgreSQL 18.6 postgres:18.6 container on 2026-09-19:

SELECT conname, conindid::regclass AS backing_index, i.indisunique
FROM pg_constraint c JOIN pg_index i ON i.indexrelid = c.conindid
WHERE c.conrelid = 'child_bookings'::regclass AND c.contype = 'f';

              conname               |          backing_index           | indisunique
------------------------------------+----------------------------------+-------------
 child_bookings_room_id_during_fkey | old_bookings_room_id_during_excl | f

Do not build on that. It is behaviour the documentation says should not be available, which makes it the kind of thing a minor release can tighten. The useful point is that if you were converting exclusion constraints only so that something could reference them, check first whether you need to. If you do, convert for a reason that will still apply after a patch release.

What did not land: FOR PORTION OF

The SQL standard also defines UPDATE ... FOR PORTION OF and DELETE ... FOR PORTION OF, which change part of a row’s period and split the row around the change. That is temporal DML, a different feature from the temporal constraints this page is about, and it is not in 18. Measured on 2026-09-19 on PostgreSQL 18.6 on a stock postgres:18.6 container:

UPDATE bookings FOR PORTION OF during
  FROM '2026-01-10' TO '2026-01-20' SET guest = 'alan';

ERROR:  syntax error at or near "FOR"

PostgreSQL 18 temporal constraints do not maintain history. They do not create rows, close a prior period, split a range or choose a new validity interval. They validate the rows that exist when you write them.

To cancel the middle ten days of a booking in one transaction, delete the original row, insert the leading remainder, and insert the trailing remainder. Put that logic in a function so it has one implementation, and let the constraint catch mistakes.

Which one should I use?

Every row below was run on 2026-09-19 against PostgreSQL 18.6 in a stock postgres:18.6 container with default settings, on Linux 6.17.0 under Docker 29.1.3. The version row adds a second environment, PostgreSQL 17.11 in a stock postgres:17.11 container, because that is the only way to measure “18 and later” rather than quote it.

	EXCLUDE USING gist	PRIMARY KEY/UNIQUE ... WITHOUT OVERLAPS	FOREIGN KEY (..., PERIOD ...)
version	9.0 and later	18 and later	18 and later
is a key	no	yes	n/a
can be referenced	documented no, measured yes	yes	n/a
any operator, not just = and &&	yes	no	no
expressions and a WHERE predicate	yes	no	no
period column type	any GiST-indexable	range or multirange only, last column	range or multirange only, last column
access method	GiST, SP-GiST and others	GiST	uses the referenced key’s index
referential actions	n/a	n/a	NO ACTION only
needs btree_gist	for scalar key columns	for scalar key columns	inherited from the referenced key

What is behind each row, in the same order.

Version. The PostgreSQL 9.0 release notes introduce the feature: “Exclusion constraints generalize uniqueness constraints by allowing arbitrary comparison operators, not just equality.” (PostgreSQL 9.0 release notes)

“18 and later” is measured rather than read off the release notes. All three of the new constructs were run on PostgreSQL 17.11 in a stock postgres:17.11 container on 2026-09-19, and all three fail to parse:

CONSTRAINT bookings_pk PRIMARY KEY (room_id, during WITHOUT OVERLAPS)
ERROR:  syntax error at or near "WITHOUT"

ALTER TABLE t17 ADD CONSTRAINT t17_u UNIQUE (a, r WITHOUT OVERLAPS);
ERROR:  syntax error at or near "WITHOUT"

FOREIGN KEY (a, PERIOD r) REFERENCES t17 (a, PERIOD r)
ERROR:  syntax error at or near "r"

A syntax error is the strong form of the claim: the 17 parser does not have this grammar at all, so no extension, compatibility setting or rewrite brings it to 17.

Is a key, and access method. One catalog query covers both rows. On 18.6:

SELECT c.conname, am.amname, i.indisunique, i.indisexclusion
FROM pg_constraint c
JOIN pg_class ic ON ic.oid = c.conindid
JOIN pg_am    am ON am.oid = ic.relam
JOIN pg_index i  ON i.indexrelid = c.conindid;

     conname      | amname | indisunique | indisexclusion
------------------+--------+-------------+----------------
 op_test_a_b_excl | gist   | f           | t
 wo_op_pkey       | gist   | t           | t
 wo_multi_pkey    | gist   | t           | t
 sp_test_r_excl   | spgist | f           | t

An exclusion constraint’s index is not unique. A temporal key’s index is flagged both unique and exclusion.

For the access method row, EXCLUDE USING spgist (r WITH &&) was accepted, so an exclusion constraint is open to other access methods. A temporal key is not, for three separate reasons, each measured on PostgreSQL 18.6 in a stock postgres:18.6 container on 2026-09-19.

There is no syntax for naming one:

ALTER TABLE am_probe ADD CONSTRAINT am_pk PRIMARY KEY USING spgist (a, r WITHOUT OVERLAPS);

ERROR:  syntax error at or near "spgist"

PRIMARY KEY USING INDEX takes the name of an existing index rather than an access method, and that route is closed too because the index it adopts has to be unique:

CREATE INDEX am_sp ON am_probe USING spgist (r);
ALTER TABLE am_probe ADD CONSTRAINT am_pk PRIMARY KEY USING INDEX am_sp;

ERROR:  "am_sp" is not a unique index
DETAIL:  Cannot create a primary key or unique constraint using such an index.

SP-GiST cannot supply a unique index in the first place:

CREATE UNIQUE INDEX am_sp_u ON am_probe USING spgist (r);

ERROR:  access method "spgist" does not support unique indexes

The supported spelling is the only one, and it picks GiST. ALTER TABLE am_probe ADD CONSTRAINT am_pk PRIMARY KEY (a, r WITHOUT OVERLAPS) succeeded, and pg_class.relam for am_pk reads gist.

Can be referenced. This is the one row that is not a clean yes or no. The PostgreSQL 18 CREATE TABLE documentation says “the referenced table must have a primary key or unique constraint declared with WITHOUT OVERLAPS”, so the documented answer for an exclusion constraint is no. Measured on 2026-09-19 on PostgreSQL 18.6 in a stock postgres:18.6 container, a FOREIGN KEY (room_id, PERIOD during) REFERENCES old_bookings (room_id, PERIOD during) against a table carrying only an EXCLUDE USING gist constraint was created and then enforced correctly, so the measured answer is yes. The section One measured behaviour that contradicts the documentation, earlier on this page, has the full test and the reason not to build on it.

Any operator. EXCLUDE USING gist (a WITH <>, b WITH =) was accepted. WITHOUT OVERLAPS has no place to put an operator: the last column is always compared with && and the others with =.

Expressions and a WHERE predicate. EXCLUDE USING gist (lower(a) WITH =, r WITH &&) WHERE (a <> 'ignore') was accepted. Both fail as syntax errors on a temporal key: PRIMARY KEY (lower(a), r WITHOUT OVERLAPS) gives ERROR: syntax error at or near "(", and appending WHERE (a <> 'x') gives ERROR: syntax error at or near "WHERE".

Period column type. int4range and int4multirange were both accepted as the last column. A plain int gives ERROR: column "b" in WITHOUT OVERLAPS is not a range or multirange type, and putting the range anywhere but last is a syntax error.

Referential actions. ON DELETE RESTRICT on a PERIOD foreign key gives ERROR: unsupported ON DELETE action for foreign key constraint using PERIOD, and so do CASCADE, SET NULL and SET DEFAULT. ON DELETE NO ACTION was accepted. The section Only NO ACTION, and that is a design constraint, earlier on this page, has the full list and what to do instead. The row is n/a for the other two columns because neither is a foreign key.

Needs btree_gist. On a fresh 18.6 database with only plpgsql installed, both forms fail identically the moment a key column is a scalar type: EXCLUDE USING gist (a WITH =, r WITH &&) and PRIMARY KEY (a, r WITHOUT OVERLAPS), with a integer, each give ERROR: data type integer has no default operator class for access method "gist". CREATE EXTENSION btree_gist makes both succeed. The foreign key adds no requirement of its own: in the same database before the extension was installed, a table whose key columns are both daterange took PRIMARY KEY (q, r WITHOUT OVERLAPS) and a child took FOREIGN KEY (pq, PERIOD r) REFERENCES p (q, PERIOD r), both without btree_gist.

Use WITHOUT OVERLAPS when something needs to reference the period, when you are on 18 or later, or when you are writing a new table. You get the temporal foreign key, and the constraint is a key, so it appears where people look for one.

Keep EXCLUDE USING gist when you are on 17 or earlier, when nothing references the table, or when the rule is not a key. Exclusion constraints remain more expressive: they support any commutative operator, expressions, and a partial predicate. A room that may not overlap in time with another room’s booking in the same building is not a key and never will be.

Do not convert existing constraints just to modernise them. Measured on 2026-09-19 on PostgreSQL 18.6 in a stock postgres:18.6 container, the old form behaves identically and is not deprecated. Converting means dropping a constraint and adding a key. Measured on 2026-09-19 on PostgreSQL 18.6 in a stock postgres:18.6 container, the ALTER TABLE ... ADD CONSTRAINT ... PRIMARY KEY (room_id, during WITHOUT OVERLAPS) held an ACCESS EXCLUSIVE lock on the table, alongside a SHARE lock for the index build, for the whole build. There is no concurrent path for it. On a large, busy table, rehearse the index build time and the lock window on representative data first.

How do I confirm what I have?

The constraint type is where the implementation shows through:

SELECT conrelid::regclass AS table_name,
       conname,
       contype,
       pg_get_constraintdef(oid) AS definition
FROM pg_constraint
WHERE connamespace = 'public'::regnamespace
  AND contype IN ('p', 'u', 'x', 'f')
ORDER BY conrelid::regclass::text, conname;

contype is x for an exclusion constraint, p for a primary key, u for unique and f for a foreign key. A temporal primary key shows as p, and pg_get_constraintdef prints the WITHOUT OVERLAPS back to you.

The index behind it is the more revealing check because it is where a temporal key stops looking like an ordinary one:

SELECT c.conname,
       c.contype,
       ic.relname AS index_name,
       am.amname  AS access_method,
       i.indisunique,
       i.indisexclusion
FROM pg_constraint c
JOIN pg_index i  ON i.indexrelid = c.conindid
JOIN pg_class ic ON ic.oid = i.indexrelid
JOIN pg_am am    ON am.oid = ic.relam
WHERE c.connamespace = 'public'::regnamespace
  AND c.contype IN ('p', 'u', 'x')
ORDER BY 1;

Both flags true indicate a temporal key. indisexclusion alone indicates a plain exclusion constraint. Neither, on a btree index, is an ordinary key.

Check that the extension is present before you write a migration that assumes it:

SELECT extname, extversion FROM pg_extension WHERE extname = 'btree_gist';

An empty result on a database where a migration is about to add a temporal key with a scalar business column means that migration will fail. This is the most common way the feature fails on the first attempt.

Why does AI-generated code cause this?

Three patterns account for most of the wasted time.

The missing extension. Generated schemas write the constraint but not CREATE EXTENSION btree_gist because the constraint is the interesting line. The migration then fails on a database where nobody has installed it. That is usually production, since somebody installed it by hand on the development box a year ago.

FOR PORTION OF. It is in the standard, so it appears in generated code, but it does not parse on 18.6. A syntax error is the good outcome here; the bad one is a plan built around it, or a generated design that assumes the database will split periods for you.

ON DELETE CASCADE on a temporal foreign key. It is the reflex on any foreign key, and it is refused here along with SET NULL, SET DEFAULT and RESTRICT. The generated schema fails at CREATE TABLE, which is cheap. The expensive version is a design that assumed the cascade and now needs an explicit lifecycle.

There is also a version gap in both directions: a model trained before 18 writes the exclusion constraint, which is correct everywhere, and one trained after may write WITHOUT OVERLAPS into a migration destined for a 17 cluster, where it is a syntax error at deploy time.

How do I stop it coming back?

Put CREATE EXTENSION IF NOT EXISTS btree_gist in the migration, next to the constraint that needs it, rather than in a runbook or a one-time setup script. Confirm that the server package ships it before you depend on it.

Decide the lifecycle before you declare the constraint. NO ACTION is the only referential action available, so you must define what happens when a parent period is shortened. The constraint will refuse the write until you do so.

Write [) and check your range type. With daterange PostgreSQL canonicalizes for you; with tsrange it does not, and [] makes adjacent periods overlap at one instant.

Test the boundaries specifically. Test adjacent ranges, ranges overlapping by one day, contained ranges, a child spanning two adjacent parents, and an unbounded parent. These are the cases where hand-written versions of this logic often fail.

Rehearse the migration on representative data, because adding the constraint holds ACCESS EXCLUSIVE for a full GiST index build with no concurrent option.

Check your target version before using the syntax, since WITHOUT OVERLAPS fails to parse on 17 and the error arrives at deploy.

How DBGorilla helps

DBGorilla can help

A temporal key is a GiST index, and a GiST index has a size and a scan count like any other. DBGorilla connects read-only and gives your AI coding agent the schema and how it changed over time, index usage and scan counts, table and index sizes, and the workload by query fingerprint, so the cost of a constraint you added is something you can see rather than assume. It surfaces and explains; it does not write to your database.

Start free

FAQ

• What is the difference between WITHOUT OVERLAPS and an exclusion constraint?

  A temporal key is enforced the same way and is a key as well. Measured on 2026-09-19 on PostgreSQL 18.6 in a stock postgres:18.6 container, the index behind UNIQUE (sku, valid_during WITHOUT OVERLAPS) has both indisunique and indisexclusion true in pg_index, where the index behind a plain EXCLUDE constraint has only indisexclusion. Being a key is what lets a PERIOD foreign key name it, and what puts it where people look for a primary key.

• Do I still need btree_gist?

  For any ordinary scalar column, yes. Measured on 2026-09-19 on PostgreSQL 18.6 in a stock postgres:18.6 container with no extensions, the only types with a default GiST operator class carrying an equality operator are range and multirange types, so an int, text or uuid business key needs btree_gist. If the non-period key column is itself a range, you do not need the extension. It is a trusted extension, so a non-superuser with CREATE on the database can enable it, provided the server package ships it.

• Does FOR PORTION OF work in Postgres 18?

  No. Measured on 2026-09-19 on PostgreSQL 18.6 in a stock postgres:18.6 container, UPDATE ... FOR PORTION OF is a syntax error. That is temporal DML rather than a constraint feature, and PostgreSQL 18 shipped only the constraints. Nothing in 18 creates history rows, closes a prior period or splits a range for you: the constraints validate the rows that exist, and writing the split is still your job.

• Is a temporal foreign key checked against the whole period?

  Yes, and against the matching parent rows taken together. Measured on 2026-09-19 on PostgreSQL 18.6 in a stock postgres:18.6 container, a child row spanning two adjacent parent bookings was accepted, while one extending past the end of every parent was rejected. The ordinary key columns match by equality first, then the matching rows' periods must cover the child period completely.

• Should I migrate my existing EXCLUDE constraints?

  Usually not. An EXCLUDE USING gist constraint works identically, measured on 2026-09-19 on PostgreSQL 18.6 in a stock postgres:18.6 container, and is not deprecated, and it can express rules a temporal key cannot, such as any operator or a WHERE predicate. Converting means dropping a constraint and adding a key, which measured on 2026-09-19 on PostgreSQL 18.6 in a stock postgres:18.6 container takes an ACCESS EXCLUSIVE lock and a full GiST index build. Do it when you want the temporal foreign key.

Related

• Which PostgreSQL index type should I use?
• Which ALTER TABLE statements lock a Postgres table?
• How to handle database migrations safely