PostgreSQL 18 引入了一項強大的新功能，允許你在時間段內強制執行唯一性：時間約束（Temporal Constraints）。通過這一新增功能，你現在可以在定義 UNIQUE、PRIMARY KEY 或 FOREIGN KEY 約束時，利用日期範圍（Date Range）或時間戳範圍（Timestamp Range）字段，比以往更容易地防止數據重疊。

問題所在：基於時間的唯一性

在最新的這個版本中，PostgreSQL 增加了一種更靈活的方式來定義 UNIQUE、PRIMARY KEY 和 FOREIGN KEY 約束。我將把示例重點放在 UNIQUE 約束上，因為這在我看來是時間約束最有用的場景。

讓我們從一個現實世界的場景開始：管理用户訂閲。

設置示例

首先，我們有一個簡單的 users 表，包含 2 行數據：

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');

使用 WITHOUT OVERLAPS 的時間約束

現在我想創建一個屬於 users 表的 subscriptions（訂閲）表。每個訂閲都有開始和結束日期，我想確保每個用户的訂閲是唯一的。用户可以有過去的訂閲（也許他們升級了層級），但在任何給定的時間點只能有一個活躍的訂閲。

以下是我們如何使用新的 WITHOUT OVERLAPS 語法來強制執行此操作：

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)
);

如你所見，我們在 valid_period 這個 daterange 字段的 UNIQUE 約束中使用了 WITHOUT OVERLAPS。時間約束要求鍵列（valid_period）必須是範圍類型（range type），因此通常你會使用 daterange 或 timestamprange，但它也適用於其他範圍類型。

測試一下

讓我們插入一些數據：

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;

結果如下：

               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)

在這個例子中，Luke 顯然在 2025 年停止了付費，而 Darth 升級到了高級版。我通常習慣使用單獨的列來表示開始和結束日期，但我很欣賞範圍類型（Ranges）能夠啓用此功能，並允許我們使用強大的範圍運算符。

額外內容：查詢活躍訂閲

使用“冰淇淋筒”運算符（@>，即包含運算符），我們可以輕鬆找到特定日期有效的訂閲：

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

這個查詢會告訴我們哪些訂閲在 2025 年 1 月 1 日是活躍的。

重要提示：GIST 與 B-Tree 索引

有一個陷阱：範圍列使用 GIST 索引，而 UUID 列使用 B-Tree 索引。當我運行 CREATE TABLE subscriptions 命令時，我遇到了一個錯誤。為了解決這個問題，你需要啓用以下擴展：

CREATE EXTENSION btree_gist;

瞭解更多

如果你想深入瞭解時間約束，請查看 PostgreSQL 文檔中關於 WITHOUT OVERLAPS 的部分。

原文鏈接：https://hashrocket.com/blog/posts/postgresql-18-temporal-cons...

作者：Vinicius Negrisolo