由 John Doe 五月 12, 2025
你是否需要在表中跟踪行随时间的变化？PostgreSQL 18 的时态性主键就可以做到这一点。
特性提交日志
添加时态性的主键和唯一约束。
在主键和唯一约束中添加WITHOUT OVERLAPS子句。这是由 GiST 索引而不是 B 树索引提供支持，因为它们本质上是排除性约束，对于键的标量部分使用=，对于时态部分使用&&。
因为'empty' && 'empty'为假，所以时态性主键/唯一约束允许重复，这会让用户感到困惑，也不符合内部预期。例如，当GROUP BY检查主键上的函数依赖时，它允许从表中选择其他列，但在存在重复键的情况下，你可能会从任意一行获取值。所以我们需要禁止空值。
这意味着目前对于时态性主键/唯一约束，我们仅支持 range 和 multirange 类型。相关的文档和测试已添加。未来，通过为其他类型引入对更通用的 “空值” 概念的支持，这一功能还可以得到扩展。
示例
在本次特性提交后，我们现在可以跟踪行随时间的变化了。而且，我们还可以让外键指向合适的行版本。
假设你有一个地址表，类似这样：
create table addresses (
id int8 generated always as identity primary key,
user_id int8 references users (id),
recipient text not null,
line1 text not null,
line2 text,
zipcode text not null,
city text not null,
country text not null,
phone text not null
);
然后你有订单表，订单的一个属性是送货地址。当然，你可以设置orders.address_id int8 references addresses (id)，但如果已经下过订单的人更改了地址，然后又下了一个新订单，会发生什么呢？
一般来说，更改应该只对第二个订单生效，第一个订单的送货数据应该保持原样。当然，这个问题有很多解决方案。例如，可以每次都创建新行，让旧版本失效。
但现在，基于本次提交的特性，我们有了更优的解决方案。具体来说，我们现在可以有如下模式：
create table addresses (
id int8 generated by default as identity,
valid_range tstzrange not null default tstzrange(now(), 'infinity', '[)'),
user_id int8 references users (id),
recipient text not null,
line1 text not null,
line2 text,
zipcode text not null,
city text not null,
country text not null,
phone text not null,
primary key (id, valid_range WITHOUT OVERLAPS)
);
注意：你需要先安装 btree_gist 扩展。
这样我就可以进行如下操作：
INSERT INTO addresses (user_id, recipient, line1, zipcode, city, country, phone)
VALUES (1, 'aa', 'aa', 'aa', 'aa', 'aa', 'aa');
INSERT INTO addresses (user_id, recipient, line1, zipcode, city, country, phone)
VALUES (1, 'bb', 'bb', 'bb', 'bb', 'bb', 'bb');
SELECT * FROM addresses;
id | valid_range | user_id | recipient | line1 | line2 | zipcode | city | country | phone
----+--------------------------------------------+---------+-----------+-------+-------+---------+------+---------+-------
3 | ["2024-09-30 12:08:52.703595+02",infinity) | 1 | aa | aa | | aa | aa | aa | aa
4 | ["2024-09-30 12:08:52.712062+02",infinity) | 1 | bb | bb | | bb | bb | bb | bb
(2 rows)
如果我需要更新地址，我会插入新版本，并更改前一个版本的范围。实际上，操作顺序相反。
BEGIN;
UPDATE addresses
SET valid_range = tstzrange( lower( valid_range ), now(), '[)')
WHERE id = 3;
INSERT INTO addresses (id, user_id, recipient, line1, zipcode, city, country, phone)
VALUES (3, 1, 'dd', 'dd', 'dd', 'dd', 'dd', 'dd');
COMMIT;
之后，查询结果如下：
SELECT * FROM addresses;
id | valid_range | user_id | recipient | line1 | line2 | zipcode | city | country | phone
----+-------------------------------------------------------------------+---------+-----------+-------+-------+---------+------+---------+-------
4 | ["2024-09-30 12:08:52.712062+02",infinity) | 1 | bb | bb | | bb | bb | bb | bb
3 | ["2024-09-30 12:08:52.703595+02","2024-09-30 12:13:32.466304+02") | 1 | aa | aa | | aa | aa | aa | aa
3 | ["2024-09-30 12:13:32.466304+02",infinity) | 1 | dd | dd | | dd | dd | dd | dd
(3 rows)
由于不能出现两行具有相同的id且valid_range重叠的数据，所以在任何时间戳下，都能够确定该行在当时的有效版本。
这也许并非适用于所有应用程序，但对于需要跟踪变化，并且能够展示任意时间点行数据状态的场景来说，这绝对是非常有用的。
确实很棒，非常感谢所有参与其中的相关人员。
参考
提交日志：https://git.postgresql.org/pg/commitdiff/fc0438b4e80535419a4e54dba87642cdf84defda