PostgreSQL 18: Mastering Temporal Constraints for Time-Based Uniqueness

PostgreSQL 18 introduces a powerful new feature, Temporal Constraints, allowing you to enforce uniqueness across time periods. This innovation enables the definition of UNIQUE, PRIMARY KEY, or FOREIGN KEY constraints using Date Range or Timestamp Range fields, significantly simplifying the prevention of overlapping data.

This article is the fourth and final installment in our series on new PostgreSQL 18 features, released approximately two months ago. If you wish to review the complete series, these are the titles:

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

PostgreSQL 18 also delivered numerous performance improvements and other enhancements.

The Challenge: Ensuring Time-Based Uniqueness

PostgreSQL 18's latest release provides a more flexible approach to defining UNIQUE, PRIMARY KEY, and FOREIGN KEY constraints. For the purpose of this article, I will focus on UNIQUE constraints, as this is where Temporal Constraints offer the most significant utility in my experience. Let's explore a practical scenario: managing user subscriptions.

Setting Up Our Example

First, we'll create a simple users table with two initial rows:

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

INSERT INTO users (email) VALUES
    ('darth@example.com'),
    ('luke@example.com');

Implementing Temporal Constraints with WITHOUT OVERLAPS

Next, we'll create a subscriptions table that references the users table. Each subscription will have defined start and end dates. Our goal is to ensure that a user can only have one active subscription at any given time, though they may have historical subscriptions (e.g., after upgrading their tier). This crucial rule can be enforced using 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 demonstrated, we're applying WITHOUT OVERLAPS within the UNIQUE constraint on a daterange field named valid_period. Temporal constraints mandate that the key column (valid_period in this case) must be a range type. While daterange or timestamprange are the most common choices, this feature is compatible with other range types as well.

Testing the Implementation

Let's populate our subscriptions table with 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 = 'darth@example.com';

SELECT * FROM subscriptions;

The resulting data will look something like this:

user_id type valid_period 0199f293-291a-70bf-b9ee-872247723d29 basic [2024-01-01,2025-01-01) 0199f293-291b-737a-9bd0-e0e0853e3377 basic [2024-01-01,2025-01-01) 0199f293-291a-70bf-b9ee-872247723d29 premium [2025-01-01,2026-01-01)

In this illustrative example, Luke's basic subscription concluded in 2025, while Darth upgraded to a premium plan. While I often use separate columns for start and end dates, I appreciate how range types facilitate this powerful feature and enable the use of robust range operators.

Bonus: Querying Active Subscriptions

Leveraging the "ice cream cone" operator (@>), we can efficiently retrieve all subscriptions active on a specific date:

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

This query effectively identifies which subscriptions were active on January 1st, 2025.

Important Note: GIST vs. B-Tree Indexes

A crucial consideration is that range columns typically utilize GIST indexes, while UUID columns use B-Tree indexes. When attempting to execute the CREATE TABLE subscriptions command, you might encounter an error if the necessary extension is not enabled. To resolve this, ensure you enable the btree_gist extension:

CREATE EXTENSION btree_gist;

Learn More

For a deeper understanding of Temporal Constraints, refer to the section on WITHOUT OVERLAPS in the official PostgreSQL documentation.

Need Expert Assistance with Your Database or Web Application?

At Hashrocket, we specialize in developing robust and performant applications using cutting-edge technologies. Whether you require 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 expert team possesses the skills to bring your project to fruition.

Get in touch with us to discuss how we can support your next project.

Related Articles

If you found this post valuable, you might also be interested in these related articles:

• PostgreSQL 18 - VIRTUAL Generated Columns
• PostgreSQL 18 - Track What Changed By Your UPSERTs
• PostgreSQL 18's UUIDv7: Faster and Secure Time-Ordered IDs