PostgreSQL 18: 时态性的外键约束

由 John Doe 五月 13, 2025

你是否需要在表中跟踪行随时间的变化？PostgreSQL 18 的时态性主键和外键就可以做到这一点。

非洲乞力马扎罗山角下的大象

特性提交日志

添加时态性的外键约束。

在外键约束定义中添加PERIOD子句。该子句支持 range 类型和 multirange 类型。时态性外键检查范围的包含关系，而非相等关系。

此功能与 SQL 标准中时态性外键的行为相匹配，但它基于 PostgreSQL 原生的 range 类型，而非 SQL 中的 “时间段”（PostgreSQL 目前还没有该概念）。

目前暂不支持ON {UPDATE,DELETE} {CASCADE,SET NULL,SET DEFAULT}这些引用的动作。

讨论：https://www.postgresql.org/message-id/flat/CA+renyUApHgSZF9-nd-a0+OPGharLQLO=mDHcY4_qQ0+noCUVg@mail.gmail.com

示例

本次提交是基于之前发布的添加时态性主键和唯一约束的补充特性。

基于之前的信息，我们现在可以创建如下面这样的表：

CREATE TABLE addresses (
    id int8 generated BY DEFAULT AS IDENTITY,
    valid_range tstzrange NOT NULL DEFAULT tstzrange(now(), 'infinity', '[)'),
    recipient text NOT NULL,
    PRIMARY KEY (id, valid_range WITHOUT OVERLAPS)
);

现在，借助这个补丁，我们可以创建一个表，使其拥有指向上述addresses表的外键：

create table orders (
    id int8 generated BY DEFAULT AS IDENTITY,
    address_id int8 not null,
    address_valid_at tstzrange not null,
    content text,
    constraint order_address foreign key ( address_id, PERIOD address_valid_at ) references addresses ( id, PERIOD valid_range )
);

PERIOD这个词是 PostgreSQL 要求的关键字（很可能是遵循 SQL 标准）。

在这种情况下，我们来看看它是如何工作的。对于address_valid_at，选择一个时间点，基本上就是下单的时刻。

那么，我们添加一些地址数据：

insert into addresses (id, valid_range, recipient) values ( 1, '[2023-01-01,2024-01-01)', 'Address from 2023' );

insert into addresses (id, valid_range, recipient) values ( 1, '[2024-01-01,2025-01-01)', 'Address from 2024' );

现在添加订单数据。首先，我们来使用无效的地址，尝试添加一个不应该成功的订单：

insert into orders (address_id, address_valid_at, content) values (1, '[2022-06-15,2022-06-15]', 'Invalid address #1');
ERROR:  insert or update on table "orders" violates foreign key constraint "order_address"
DETAIL:  Key (address_id, address_valid_at)=(1, ["2022-06-15 00:00:00+08","2022-06-15 00:00:00+08"]) is not present in table "addresses".

insert into orders (address_id, address_valid_at, content) values (2, '[2024-06-15,2024-06-15]', 'Invalid address #2');
ERROR:  insert or update on table "orders" violates foreign key constraint "order_address"
DETAIL:  Key (address_id, address_valid_at)=(2, ["2024-06-15 00:00:00+08","2024-06-15 00:00:00+08"]) is not present in table "addresses".

到目前为止一切正常。接下来，我们插入两个分别对应不同地址的有效订单：

insert into orders (address_id, address_valid_at, content) values (1, '[2023-06-15,2023-06-15]', 'Order with address from 2023');

insert into orders (address_id, address_valid_at, content) values (1, '[2024-06-15,2024-06-15]', 'Order with address from 2024');

成功插入。现在的问题是，如何查询数据来连接这两个表呢？

select
    o.id,
    o.content,
    a.id,
    a.recipient
from
    orders o
    join addresses a on o.address_id = a.id and a.valid_range @> o.address_valid_at
;
 id |           content            | id |     recipient     
----+------------------------------+----+-------------------
  3 | Order with address from 2023 |  1 | Address from 2023
  4 | Order with address from 2024 |  1 | Address from 2024
(2 rows)

一切都很简单、清晰且功能正常，当然，前提是你了解范围运算符。

为了完整性，我们看看如果尝试删除一个被某个订单使用过的地址，会发生什么：

delete from addresses where recipient = 'Address from 2023';
ERROR:  update or delete on table "addresses" violates foreign key constraint "order_address" on table "orders"
DETAIL:  Key (id, valid_range)=(1, ["2023-01-01 00:00:00+08","2024-01-01 00:00:00+08")) is still referenced from table "orders".