by Vinicius Negrisolo on November 6, 2025

PostgreSQL 18 introduced a powerful new feature that lets you enforce uniqueness across time periods: Temporal Constraints. With this addition, you can now define UNIQUE, PRIMARY KEY, or FOREIGN KEY constraints by either Date Range or Timestamp Range fields, making it easier than ever to prevent overlapping data.

This is the 4th and final post in our series about new features in PostgreSQL 18, released about 2 months ago. In case you want to catch up on the whole series, here are the links:

• PG 18 - UUIDv7
• PG 18 - OLD and NEW for tracking UPSERTs
• PG 18 - VIRTUAL Generated Columns

PostgreSQL also released a bunch of performance improvements and other features, check them out.

In this latest release, PostgreSQL added a more flexible way to define UNIQUE, PRIMARY KEY, and FOREIGN KEY constraints. I'll focus my examples on the UNIQUE constraint, as that's where I see temporal constraints being most useful for my cases.

Let's start with a real-world scenario: managing user subscriptions.

First, we have a simple users table with 2 rows:

CREATE TABLE users (
  id uuid DEFAULT uuidv7() PRIMARY KEY,
  email VARCHAR(100) UNIQUE NOT NULL
);

INSERT INTO users (email)
VALUES
  ('[email protected]'),
  ('[email protected]');

Now I want to create a subscriptions table that belongs to the users table. Each subscription will have start and end dates, and I want to ensure it's unique per user. Users can have past subscriptions (maybe they upgraded their tier), but only a single active subscription at any given time. Here's how we can enforce that with the new WITHOUT OVERLAPS syntax:

CREATE TABLE subscriptions (
  user_id uuid NOT NULL,
  type VARCHAR(50) NOT NULL,
  valid_period daterange NOT NULL,
  FOREIGN KEY (user_id) REFERENCES users(id),
  UNIQUE (user_id, valid_period WITHOUT OVERLAPS)
);

As you can see, we're using WITHOUT OVERLAPS in the UNIQUE constraint over a daterange field called valid_period. Temporal constraints require the key column (valid_period) to be a range type, so you'll typically use either daterange or timestamprange, but it does work with other range types as well.

Let's insert some data:

INSERT INTO subscriptions (user_id, type, valid_period)
SELECT u.id, 'basic', daterange('2024-01-01', '2025-01-01', '[)')
FROM users u;

INSERT INTO subscriptions (user_id, type, valid_period)
SELECT u.id, 'premium', daterange('2025-01-01', '2026-01-01', '[)')
FROM users u
WHERE u.email = '[email protected]';

SELECT * FROM subscriptions;

This gives us:

In this example, Luke apparently stopped paying for the service in 2025, while Darth upgraded to premium. I typically use separate columns for start and end dates, but I appreciate that ranges enable this feature and let us use powerful range operators.

Using the "ice cream cone" operator, we can easily find valid subscriptions on a specific date:

SELECT *
FROM subscriptions
WHERE valid_period @> '2025-01-01'::date;

This query shows us which subscriptions were active on January 1st, 2025.

One gotcha: range columns use GIST indexes and UUID columns use B-Tree indexes. When I ran the CREATE TABLE subscriptions command, I got an error. To solve this, you need to enable the extension:

CREATE EXTENSION btree_gist;

If you want to dive deeper into Temporal Constraints, check out the section about WITHOUT OVERLAPS in the PostgreSQL documentation.

At Hashrocket, we specialize in building robust, performant applications using modern technologies. Whether you need help optimizing your PostgreSQL database, building features with Elixir and Phoenix, developing with Ruby on Rails, or creating dynamic user interfaces with React and React Native, our team has the expertise to bring your project to life. Get in touch with us to discuss how we can help with your next project.