好吧,我最终这样做了:
CREATE TABLE test (
from_ts TIMESTAMPTZ,
to_ts TIMESTAMPTZ,
account_id INTEGER DEFAULT 1,
product_id INTEGER DEFAULT 1,
CHECK ( from_ts < to_ts ),
CONSTRAINT overlapping_times EXCLUDE USING GIST (
account_id WITH =,
product_id WITH =,
period(from_ts, CASE WHEN to_ts IS NULL THEN 'infinity' ELSE to_ts END) WITH &&
)
);
与无限,交易证明完美配合。
我只需要安装时间扩展,它将在postgres 9.2中是本地的,而btree_gist在9.1中可以作为扩展 CREATE EXTENSION btree_gist;