What's new with constraints in Postgres 18
summary
Postgres 18 adds concrete, usable features: temporal keys WITHOUT OVERLAPS lets a composite key include a range/multirange (GiST + gist_range_ops) to prevent overlapping validity periods for bookings or salaries; temporal foreign keys use PERIOD ... DURING to require referenced rows cover the referencing period (ON UPDATE/DELETE cascades not yet supported). NOT ENFORCED records constraints in pg_constraint without creating triggers or validating existing rows, and NOT NULL as a first-class constraint adds convalidated/conenforced flags and partition-aware NOT VALID handling.
AI-written summary · about
chapters (13)
- 0:00Music & introduction
- 0:37Agenda: temporal keys and constraint changes
- 1:14What constraints are and why they matter
- 3:50Overview of Postgres 18 constraint updates
- 4:56Understanding temporal databases and time models
- 7:04Semantics of time: valid time vs transaction time explained
- 9:34Temporal queries and data modeling shift
- 11:22Temporal primary keys and no-overlap rules
- 12:50Booking example with temporal constraints
- 15:41Temporal foreign keys and referential integrity
- 17:50NOT ENFORCED constraints and schema flexibility
- 21:26NOT NULL as a first-class constraint
- 24:45Partition table improvements and wrap-up
related talks
41mWhat you should know about constraints in PostgreSQL 18
43mWhat You Should Know About Constraints in PostgreSQL (and What’s New in 18
46mEnhancements to the bitemporal model support: integrity constraints
50mRange Types in PostgreSQL 9.2 - Your Life Will Never Be the Same
46mIntro to Postgres Hacking via Temporal Tables
23mImplementing Temporal Features in PostgreSQL: SQL Standard and Beyond
44mPostgreSQL in Django 1.8
30mLesser known features of PostgreSQL