Repository navigation
Document PostgreSQL 18 temporal keys over a range column: PRIMARY KEY … WITHOUT OVERLAPS and FOREIGN KEY … PERIOD #97
Description
PostgreSQL 18 adds temporal keys over a range column: PRIMARY KEY (id, during WITHOUT OVERLAPS) / UNIQUE (… WITHOUT OVERLAPS) and FOREIGN KEY (room_id, PERIOD during) REFERENCES room (id, PERIOD during) (release notes, https://www.postgresql.org/docs/18/release-18.html; CREATE TABLE, https://www.postgresql.org/docs/18/sql-createtable.html). They are the built-in replacement for the exclusion-constraint recipe docs/efcore.md#reject-overlapping-rows-with-an-exclusion-constraint documents through a third-party package, and they add referential integrity across time, which an exclusion constraint cannot give.
Verified live on 18.4 with btree_gist installed: both constraints create over a daterange column; inserting (1, [2026-01-15,2026-03-01)) next to (1, [2026-01-01,2026-02-01)) fails with 23P01 conflicting key value violates exclusion constraint "room_pkey".
The EF side. Npgsql EF provider 11 (rc as of 2026-10-17) adds the model-building API: HasKey(…).HasWithoutOverlaps() and HasForeignKey(…).HasPrincipalKey(…).HasPeriod(), documented with NpgsqlRange<DateTime> properties (https://www.npgsql.org/efcore/release-notes/11.0.html, "Support for PostgreSQL 18 temporal constraints"). Npgsql 10, the current pin, has nothing, so a migration needs migrationBuilder.Sql(…) and EnsureCreated() cannot create it, as the docs already say for exclusion constraints.
Scope since 2026-10-18 (two lines, epic #147): this issue is the 10.x half.
On the 10.x line (Npgsql 10) the migration no longer needs migrationBuilder.Sql(…): EFCore.ComplexIndexes.PostgreSQL (same author) exposes HasTemporalConstraint, which accepts any period whose store type ends in range, and every mapping of this package carries that name. The docs/efcore.md section is written against that API, next to the existing exclusion-constraint section, measured the same way (the rejected overlap, the accepted adjacent pair, the foreign-key violation, the DbUpdateException shape, the btree_gist requirement). The measured proof lives in #154 (cross-tests with ComplexIndexes); this issue holds the documentation and its claims.
The Npgsql 11 half, native HasWithoutOverlaps() / HasPeriod() over this package's types, is #150 under the 11.0.0 epic and is the preferred form on that line.
No helper belongs in this package on either line: the API is the provider's, or ComplexIndexes'.
Done when: the 10.x section exists with every statement measured against PostgreSQL 18, and claims.json holds its promises (held by #154's tests).
Found by the PostgreSQL catalogue sweep (18 vs 17 diff), 2026-10-17.
Activity
CaffeinatedCoder commentedon Oct 18, 2026
Rescoped 2026-10-18 for the two-line plan (epic #147):
migrationBuilder.Sql(…):EFCore.ComplexIndexes.PostgreSQL(same author) exposesHasTemporalConstraint, and it accepts any period whose store type ends inrange, which every mapping of this package carries. This issue keeps thedocs/efcore.mdsection for the 10.x line, written against that API next to the existing exclusion-constraint section; the measured proof lives in the cross-test issue under the backlog ("Cross-tests with EFCore.ComplexIndexes").HasWithoutOverlaps()/HasPeriod()over this package's types, preferred on that line.The sentence "No helper belongs in this package: the API is the provider's" still holds on both lines.
CaffeinatedCoder commentedon Oct 18, 2026
Measured in #159 (
ComplexIndexesCrossTests, PostgreSQL 18.x alpine,EFCore.ComplexIndexes.PostgreSQL5.4.0, Npgsql EF 10.0.0), for the 10.x section this issue writes:HasTemporalConstraint(b => b.RoomId, b => <col>, name)over aDateRange, aRangeSet<DateRange, DateOnly>and aTimeRange(customtimerange) rendersUNIQUE ("RoomId", "<col>" WITHOUT OVERLAPS), andpg_get_constraintdefreads back the same text.btree_gistis injected by that package's differ.DbUpdateExceptionwrappingPostgresException23P01, withConstraintNameset to that constraint. Adjacent rows (including adjacent multiranges) and rows for another key are saved.EnsureCreated()does create these and the exclusion constraints onceUseNpgsqlComplexIndexes()is on the options (it registers the package's differ for the runtime path, since 5.1.0). Without it there are none, and overlaps are saved. The current box indocs/efcore.md#reject-overlapping-rows-with-an-exclusion-constraintgives only "Use migrations, or add the constraint with explicit SQL". The section should name the registration as the third way.When the section lands, its sentences can name
ComplexIndexesCrossTestsinheldBy; E40/E41 already hold the exclusion and temporal measurements.