PostgreSQL Temporal Constraints: WITHOUT OVERLAPS and PERIOD

Atlas now supports PostgreSQL 18 temporal keys: PRIMARY KEY and UNIQUE constraints declared with WITHOUT OVERLAPS, and FOREIGN KEY constraints declared with PERIOD. They are inspected, diffed, planned, and described in HCL or SQL schema like any other constraint.

PostgreSQL 18 added temporal keys: PRIMARY KEY and UNIQUE constraints declared with WITHOUT OVERLAPS, which enforce uniqueness over non-overlapping time ranges, and FOREIGN KEY constraints declared with PERIOD, which enforce referential integrity over those ranges. Atlas now supports all three: they are inspected, diffed, planned, and described in HCL or SQL schema like any other constraint.

Declaring Temporal Keys in HCL

Two new boolean attributes, without_overlaps and period, correspond to the clauses above. Each marks the block's last column, or for a foreign_key, the last referencing and referenced column, since PostgreSQL only permits the clause there and a boolean loses no information:

table "parent" {  schema = schema.public  column "id" {    null = false    type = int  }  column "validity" {    null = false    type = tsrange  }  primary_key {    columns          = [column.id, column.validity]    type             = GIST    without_overlaps = true  }  unique "parent_key" {    columns          = [column.id, column.validity]    type             = GIST    without_overlaps = true  }}
table "child" {  schema = schema.public  column "parent_id" {    null = false    type = int  }  column "validity" {    null = false    type = tsrange  }  foreign_key "child_parent_fkey" {    columns     = [column.parent_id, column.validity]    ref_columns = [table.parent.column.id, table.parent.column.validity]    period      = true  }}

type = GIST may be omitted when authoring, as a temporal key is always backed by a GiST index and Atlas fills it in. Inspection emits it, since GiST is not the default index type. As part of this change, the unique block gained a type attribute, so an inspected non-BTREE unique constraint now round-trips, and primary_key blocks emit type as well.

Generated SQL

atlas migrate diff against PostgreSQL 18 generates this SQL for a schema with a temporal primary key, a temporal unique constraint with INCLUDE columns, and a temporal foreign key:

-- Create "parent" tableCREATE TABLE "parent" (  "id" integer NOT NULL,  "room" integer NOT NULL,  "validity" tsrange NOT NULL,  PRIMARY KEY ("id", "validity" WITHOUT OVERLAPS),  CONSTRAINT "parent_room_validity_id_key" UNIQUE ("room", "validity" WITHOUT OVERLAPS) INCLUDE ("id"));-- Create "child" tableCREATE TABLE "child" (  "parent_id" integer NOT NULL,  "validity" tsrange NOT NULL,  CONSTRAINT "child_parent_id_validity_fkey" FOREIGN KEY ("parent_id", PERIOD "validity")    REFERENCES "parent" ("id", PERIOD "validity") ON UPDATE NO ACTION ON DELETE NO ACTION);

Note the clause placement: WITHOUT OVERLAPS sits inside the key parentheses while INCLUDE follows them, and PERIOD marks the range column on both sides of the foreign key. No USING GIST clause is emitted: the constraint grammar does not accept one, even though the backing index is GiST.

Inspection and Diffing

Inspection is driven by pg_constraint.conperiod, which PostgreSQL sets for both forms. The exclusion operators PostgreSQL derives for a temporal key (equality on the leading columns, && on the range column) are not surfaced: unlike EXCLUDE constraints, the PRIMARY KEY and UNIQUE grammar has no place to write them, so keeping them would leave every inspect-versus-HCL diff dirty.

Adding or removing the temporal property on an existing key is a constraint change: Atlas detects it in schema diff and migrate diff, and plans it as a drop and add of the constraint.

Requirements and Limitations

• PostgreSQL 18 or above. CockroachDB and YugabyteDB do not support temporal keys.
• The btree_gist extension is required whenever a key mixes scalar and range columns, since GiST has no default operator class for types like integer without it.
• Converting a temporal PRIMARY KEY or FOREIGN KEY to a plain one in a single migration is rejected by PostgreSQL: a plain foreign key needs a non-temporal unique constraint to bind to, and a WITHOUT OVERLAPS key cannot satisfy one. Converting a temporal UNIQUE constraint works.

Getting Started

Temporal constraints are part of Atlas Pro:

$ atlas login

Without a login, inspection reads a temporal key as the plain GiST key backing it and reports no drift, and planning a hand-authored one fails with an error that points to atlas login. See the HCL reference for the new attributes on the primary_key, unique, and foreign_key blocks.