# 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. ```sql CREATE EXTENSION IF NOT EXISTS btree_gist; ``` ```sql 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](https://www.postgresql.org/docs/18/sql-createtable.html) 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](https://www.postgresql.org/docs/18/btree-gist.html) 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: ```sql 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: ```sql 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](https://www.postgresql.org/docs/18/sql-createtable.html) 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: ```sql 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 ```sql 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](https://www.postgresql.org/docs/18/sql-createtable.html) 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](https://www.postgresql.org/docs/18/sql-createtable.html) 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: ```sql 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: ```sql 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](https://www.postgresql.org/docs/release/9.0.0/)) "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: ```sql 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](https://www.postgresql.org/docs/18/sql-createtable.html) 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: ```sql 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: ```sql 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: ```sql 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 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. [Get started free →](https://app.dbgorilla.com/signup) ## Related - [Which PostgreSQL index type should I use?](https://www.dbgorilla.com/learn/postgres/which-postgresql-index-type-should-i-use/) - [Which ALTER TABLE statements lock a Postgres table?](https://www.dbgorilla.com/learn/postgres/which-alter-table-statements-lock-a-postgres-table/) - [How to handle database migrations safely](https://www.dbgorilla.com/learn/postgres/how-to-handle-database-migrations-safely/)