Postgres日期重叠约束

Question

Column1	Column2	Column3
Value1	Value2	Value3

date_start    date_end     account_id    product_id
2001-01-01    2001-01-31   1             1
2001-02-01    2001-02-20   1             1
2001-04-01    2001-05-20   1             1

我希望禁止在给定的 (account_id, product_id) 中出现重叠的时间间隔。

编辑：我找到了一些资料：

CREATE TABLE test (                                                                                                
    from_ts TIMESTAMPTZ,
    to_ts TIMESTAMPTZ,
    account_id INTEGER,
    product_id INTEGER,
    CHECK ( from_ts < to_ts ),
    CONSTRAINT overlapping_times EXCLUDE USING GIST (
        account_id WITH =,
        product_id WITH =,
        box(
            point( extract(epoch FROM from_ts at time zone 'UTC'), extract(epoch FROM from_ts at time zone 'UTC') ),
            point( extract(epoch FROM to_ts at time zone 'UTC') , extract(epoch FROM to_ts at time zone 'UTC') )
        ) WITH &&
    )
);

如果你想了解更多关于这个的信息，请访问http://www.depesz.com/2010/01/03/waiting-for-8-5-exclusion-constraints/。

我的唯一问题是它不能处理空值作为结束时间戳，我考虑用无限值代替，但也不起作用。

- yokoloko

---

你想禁止重叠的区间吗？ - wildplasser

没错，这正是我要找的词。 - yokoloko

дЅ еє”иЇҐдЅїз”ЁCREATE CONSTRAINT TRIGGER并编写一个特定的函数来执行你的检查。 - vyegorov

这里也发布了将时间间隔映射为2D几何图形的技巧（舍入到浮点数有问题）。但说实话：创建触发器(或类似于我贡献中的规则+金丝雀表)是正确的方式。你甚至可以用它来捕捉 end_date IS NULL 的情况。而且比较是精确的。注意: 不要使用规则，除非你知道自己在做什么;-] - wildplasser

我认为事务和触发器存在问题，触发器不知道同时运行的其他事务。 - yokoloko

Answer 1

好的，最终我做了以下操作：

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的本机扩展，并在9.1中作为扩展提供的btree_gist即可。CREATE EXTENSION btree_gist;

注意：如果您没有空时间戳，则无需使用临时扩展，您可以按照我的问题中指定的框方法进行操作。

- yokoloko

---

+1 这是 排除约束 的完美使用案例（在 Postgres 9.0 中新引入）。您可以定义列 to_ts TIMESTAMPTZ NOT NULL DEFAULT 'infinity'，而不是使用 CASE 语句。同样，对于 from_ts，可以使用 '-infinity'。 - Erwin Brandstetter

我一开始想把无限设为默认值，但这会改变行为。我不确定它对我的代码会产生什么影响，因为我从未使用过无限时间戳。尽管在这种情况下使用无限可能更有意义。 - yokoloko

随着9.2版本的发布，范围类型已被添加，包括日期范围，这简化了您的约束和整个问题域。http://www.postgresql.org/docs/devel/static/rangetypes.html - toxaq

你可以使用 COALESCE(to_ts, 'infinity'::timestamptz) 而不是 CASE WHEN，这样可以减少 UPPERCASE WORD SALAD。 - Martijn

我尚未尝试过，但看起来在9.2版本中可以通过将 period() 函数替换为 tstzrange() 函数而无需安装时间扩展程序来实现此操作。 - Peeja

更好的是，9.2的范围类型将NULL视为正无穷或负无穷。因此，您可以直接使用to_ts，而无需使用CASE或COALESCE。 - Peeja

Answer 2

在最新的PostgreSQL版本中（我在9.6中测试过，但我认为它适用于>=9.2），您可以使用内置函数，如其他评论中所述。默认情况下，空值将被视为正无穷或负无穷，因此不再需要CHECK约束（如果您可以接受检查仅为< =，并且范围可以以相同的日期开始和结束）。仅需要扩展：

CREATE EXTENSION btree_gist;

CREATE TABLE test (
    from_ts TIMESTAMPTZ,
    to_ts TIMESTAMPTZ,
    account_id INTEGER DEFAULT 1,
    product_id INTEGER DEFAULT 1,
    CONSTRAINT overlapping_times EXCLUDE USING GIST (
        account_id WITH =,
        product_id WITH =,
        TSTZRANGE(from_ts, to_ts) WITH &&
    )
);

- Ralf

---

TSTZRANGE范围类型在PostgreSQL中不是不可变的（因为它取决于PostgreSQL服务器的时区），而且PostgreSQL不允许我使用非不可变函数创建约束。如果我的用例只需要比较没有时区的日期，那么我可以使用DATERANGE或TSRANGE。 - Paul B.

@PaulB. 那可能是一些更新版本的问题吗？我记得3年前我成功地完成了以上操作... - Ralf

嗨@mineralf，实际上我再次检查了一下，我的初始表格有DATE时间（因此没有时区信息）。在这种情况下，尝试使用TSTZRANGE函数添加约束会导致此错误：functions in index expression must be marked IMMUTABLE。简而言之：

• 如果您的列类型为DATE/TIMESTAMP，请在约束中使用TSRANGE作为函数
• 如果您的列类型为TIMESTAMPTZ，请在约束中使用TSTZRANGE作为函数

- Paul B.

Answer 3

这是一个困难的问题，因为约束只能引用“当前行”，并且不能包含子查询。（否则，解决方案就是在检查中添加一些NOT EXISTS()子查询）

作为列约束指定的检查约束应仅引用该列的值，而出现在表约束中的表达式可以引用多个列。

目前，CHECK表达式不能包含子查询，也不能引用除当前行的列之外的变量。

流行的解决方法是：使用触发器函数来完成脏工作（或使用规则系统，但大多数人都已经弃用了它）

因为大多数人喜欢触发器，所以我会在这里重新发布一个规则系统hack...（它没有额外的“id”关键元素，但这是一个小细节）

-- Implementation of A CONSTRAINT on non-overlapping datetime ranges
-- , using the Postgres rulesystem.
-- We need a shadow-table for the ranges only to avoid recursion in the rulesystem.
-- This shadow table has a canary variable with a CONSTRAINT (value=0) on it
-- , and on changes to the basetable (that overlap with an existing interval)
-- an attempt is made to modify this variable. (which of course fails)

-- CREATE SCHEMA tmp;
DROP table tmp.dates_shadow CASCADE;
CREATE table tmp.dates_shadow
    ( time_begin timestamp with time zone
    , time_end timestamp with time zone
    , overlap_canary INTEGER NOT NULL DEFAULT '0' CHECK (overlap_canary=0)
    )
    ;
ALTER table tmp.dates_shadow
    ADD PRIMARY KEY (time_begin,time_end)
    ;

DROP table tmp.dates CASCADE;
CREATE table tmp.dates
    ( time_begin timestamp with time zone
    , time_end timestamp with time zone
    , payload varchar
    )
    ;

ALTER table tmp.dates
    ADD PRIMARY KEY (time_begin,time_end)
    ;

CREATE RULE dates_i AS
    ON INSERT TO tmp.dates
    DO ALSO (
    -- verify shadow
    UPDATE tmp.dates_shadow ds
        SET overlap_canary= 1
        WHERE (ds.time_begin, ds.time_end) OVERLAPS ( NEW.time_begin, NEW.time_end)
        ;
    -- insert shadow
    INSERT INTO tmp.dates_shadow (time_begin,time_end)
        VALUES (NEW.time_begin, NEW.time_end)
        ;
    );


CREATE RULE dates_d AS
    ON DELETE TO tmp.dates
    DO ALSO (
    DELETE FROM tmp.dates_shadow ds
        WHERE ds.time_begin = OLD.time_begin
        AND ds.time_end = OLD.time_end
        ;
    );

CREATE RULE dates_u AS
    ON UPDATE TO tmp.dates
    WHERE NEW.time_begin <> OLD.time_begin
    AND NEW.time_end <> OLD.time_end
    DO ALSO (
    -- delete shadow
    DELETE FROM tmp.dates_shadow ds
        WHERE ds.time_begin = OLD.time_begin
        AND ds.time_end = OLD.time_end
        ;
    -- verify shadow
    UPDATE tmp.dates_shadow ds
        SET overlap_canary= 1
        WHERE (ds.time_begin, ds.time_end) OVERLAPS ( NEW.time_begin, NEW.time_end)
        ;
    -- insert shadow
    INSERT INTO tmp.dates_shadow (time_begin,time_end)
        VALUES (NEW.time_begin, NEW.time_end)
        ;
    );


INSERT INTO tmp.dates(time_begin,time_end) VALUES
  ('2011-09-01', '2011-09-10')
, ('2011-09-10', '2011-09-20')
, ('2011-09-20', '2011-09-30')
    ;
SELECT * FROM tmp.dates;


EXPLAIN ANALYZE
INSERT INTO tmp.dates(time_begin,time_end) VALUES ('2011-09-30', '2011-10-04')
    ;

INSERT INTO tmp.dates(time_begin,time_end) VALUES ('2011-09-02', '2011-09-04')
    ;

SELECT * FROM tmp.dates;
SELECT * FROM tmp.dates_shadow;

                                                                                                                      

- wildplasser

---

我认为可以通过排除约束条件来做一些事情。不过不确定，你可以看看我的编辑。 - yokoloko

Answer 4

如何为一组列创建唯一约束：

 CREATE TABLE table (
    date_start date,
    date_end  date,
    account_id integer,
    UNIQUE (account_id , date_start ,date_end) );

在您的情况下，如果表已经存在，您需要使用 ALTER TABLE 命令进行修改，请查看文档以获取帮助：
- DDL 约束
- ALTER Table

- mcha

---

pg-8.1是相当老的版本。 - wildplasser

嗯，这不是我正在寻找的。我正在寻找一些可以禁止重叠间隔的东西。但还是谢谢。 - yokoloko