PostgreSQL as a temporal database

PostgreSQL 18 introduced a lot of improvements around constraints but temporal keys got me most excited. With the release of PostgreSQL 19 Beta 1 on June 4, PostgreSQL continues to build on that foundation with even more temporal features. It feels like PostgreSQL is steadily closing the gap toward becoming a truly temporal database.

In this post, we'll explore non-overlapping primary and unique keys with the WITHOUT OVERLAPS and how we can achieve temporal referential integrity with foreign key constraints with the PERIOD. We'll also look at PostgreSQL 19's UPDATE/DELETE ... FOR PORTION OF which let you modify or remove application-time history while automatically preserving the unaffected time periods.

If you want to read more about constraints in general, I wrote a blog post about what you should know about constraints in PostgreSQL.

What makes a database temporal

Before getting into temporal keys, let's step back and think about what makes a database temporal. Temporal databases support managing and accessing temporal data by providing one or more of the following features:

As usual, whenever I learn about a new database concept, I end up discovering that PostgreSQL already supports a large part of it either directly or by providing most of the necessary building blocks. In the table above (based on PostgreSQL 18), I marked the new temporal keys in purple. A green check is for what is fully supported and yellow warning sign is for partial support.

PostgreSQL provides built-in range types such as daterange, tsrange and tstzrange for representing intervals between two values. These types support infinite bounds, making it possible to model "no end" or "valid forever" periods naturally. While PostgreSQL does not currently implement a dedicated SQL-standard PERIOD data type, its range types provide much of the same functionality.

When it comes to how "time" is treated temporal model and relational model are quite different. In temporal databases, time is a must-have dimension and they have different time concepts like valid time and system (transaction) time.

On the valid time side (also called application or business time), PostgreSQL is already quite strong. Range types combined with constraints and temporal predicates allow modeling non-overlapping intervals, validity periods and temporal relationships in a fairly natural way. On the transaction time side (system-managed history and automatic versioning), PostgreSQL still does not provide native support. Features such as automatic row versioning, built-in history tables and native time-travel queries still typically require triggers, audit tables or extensions.

PostgreSQL 18 closed an important gap by introducing temporal primary keys and unique constraints with WITHOUT OVERLAPS, together with temporal foreign keys using PERIOD clause. These features make temporal integrity constraints declarative and enforce them directly in the database engine.

However, PostgreSQL still lacks native system-versioned tables and SQL-standard temporal query syntax such as FOR SYSTEM_TIME AS OF.

Why temporal matters

There is one key temporal concept: For the same business key, valid time periods must not overlap. And this is exactly what WITHOUT OVERLAPS clause does starting with PostgreSQL 18.

I first learned this principle from Boris Novikov when I hosted him at the Prague PostgreSQL Meetup in last June. In his Querying Temporal Data presentation he described temporal databases as systems that allow true point-in-time queries while preserving the history of how data evolves over time; representing valid time using closed-open periods ([start, end)), ensuring adjacent versions don't collide. Those are exactly the semantics we can now declare with DDL.

In the relational model, a table typically stores only the latest value. You lose the history, making it impossible to answer questions such as: What was the employee's salary in March? When did it change? Was there ever a gap or an overlap in the recorded salary periods? With valid time, the time period becomes part of the data model and the database can answer temporal questions naturally:

Temporal PRIMARY KEY and UNIQUE constraints

SQL:2011 standard introduced temporal features and now Postgres adds support for temporal primary keys and unique constraints by making use of WITHOUT OVERLAPS clause:

First thing to know is, the WITHOUT OVERLAPS option is only meant for the last listed column and it is a range or multirange type. Then Postgres will enforce that, for the same scalar keys (in this case id), the time periods in valid_at never overlap.

So, we can have multiple entries with the same id value but each of these can only be valid_at in different time periods.

Let's run some queries to see how it works, but first we need to install btree_gist extension:

Consider a scheduling system where each room can only have one booking at a time, so let's create rooms and bookings tables:

Now, let's enter two rooms and for the Blue room we'll be able to insert adjacent ranges:

But, if we try to insert a value that overlaps for the same room_id it will then fail:

But, different room_id can overlap, let's see for the Green room:

And also empty ranges should be rejected by temporal PK rules (anyway an empty range does not make sense):

So, with WITHOUT OVERLAPS, Postgres enforces that no two bookings overlap in time for the same room. This moves logic that used to require custom exclusion constraints into the familiar PRIMARY KEY/UNIQUE framework.

Temporal FOREIGN KEY constraints

For temporal data, we need temporal foreign keys. A regular key enforces a simple rule: for every referencing row, there exists a referenced row with the same key. This works because equality (=) is the right notion of matching for timeless facts.

But temporal data breaks that assumption. In temporal models, rows are valid over time periods, so the referential integrity must hold for a period not at a single point. Then the rule becomes: for every referencing row, the referenced row must exist and be valid for the entire period in question. This is what PERIOD clause introduces for foreign key constraints.

Building on our scheduling system example with rooms and bookings tables, we could introduce a room_availability table. We want to enforce the following business rules:

• A room is available during a specific time period
• A booking is valid during a specific time period
• A booking is valid only if its entire validity period falls within a room's availability period.

We can then add a temporal foreign key on bookings table, since as a booking depends on room availability. The referenced table room_availability needs a temporal primary key using WITHOUT OVERLAPS and the booking's PERIOD during must reference PERIOD available.

Want to learn more?

This post is based on my talk at Berlin Buzzwords, presented on June 9. You can watch the recording here.

Berlin Buzzwords, 9 June 2026