SQL CHECK约束以防止日期重叠

Question

我有一个表格，描述了一台机器在不同时间安装的软件版本：

machine_id::integer, version::text, datefrom::timestamp, dateto::timestamp

我希望实施一个限制条件，以确保没有日期范围重叠，即不可能在同一时间将多个软件版本安装在一台机器上。

如何在SQL中实现这一目标？我正在使用PostgreSQL v8.4。

- Michael

Answer 1

在PostgreSQL 8.4中，这只能通过触发器来解决。触发器必须在插入/更新时检查是否存在冲突的行。由于事务可串行性没有实现谓词锁定，因此您需要自己执行必要的锁定。为了做到这一点，请使用 SELECT FOR UPDATE 选取machines表中的行，以便没有其他事务可以同时插入可能会冲突的数据。

在PostgreSQL 9.0中，将有一个更好的解决方案，称为排除约束（在CREATE TABLE下略有记录）。它将让你指定日期范围不能重叠的约束条件。作者Jeff Davis在这个特性上有两部分的描述：part 1和part 2。Depesz也有一些关于这个特性的代码示例。code examples describing the feature。

- Ants Aasma

Answer 2

与此同时（如果我正确地阅读手册，自9.2版本以来），postgreSQL已经为范围类型添加了支持。

使用这些范围类型，问题突然变得非常简单（以下示例摘自手册）：

CREATE TABLE reservation (
    during tsrange,
    EXCLUDE USING gist (during WITH &&)
);

就是这样。测试（也是从手册中复制的）：

INSERT INTO reservation VALUES
    ('[2010-01-01 11:30, 2010-01-01 15:00)');

插入 0 1

INSERT INTO reservation VALUES
    ('[2010-01-01 14:45, 2010-01-01 15:45)');

错误：冲突的键值违反了排除约束“reservation_during_excl” 详细信息：键（during）=（["2010-01-01 14:45:00"，“2010-01-01 15:45:00")) 冲突 已存在的键（during）=（["2010-01-01 11:30:00"，“2010-01-01 15:00:00")）。

- yankee

---

如何为具有外键的唯一行设置EXCLUDE语句，例如我们有一个CREATE TABLE reservation ( resourceId integer NOT NULL, during tsrange, EXCLUDE USING gist (during WITH &&) ); - mike james

@mikejames：像这样：exclude using gist ( resourceId with =, during with &&)。 - yankee

要在标量值中使用EXCLUDE，请启用：CREATE EXTENSION btree_gist; 然后，使用gist进行排除（resourceId with =，during with &&）。 - Mahdi Hamzeh

Answer 3

CREATE EXTENSION IF NOT EXISTS btree_gist; 

CREATE TABLE machines(
    machine_id integer, 
    version text, 
    during tsrange,
    EXCLUDE USING gist ( machine_id with =, during with &&)
);

表格中具有相同ID的机器将不会重叠。

- Mahdi Hamzeh

---

我在machin_id上遇到了一个错误，说整数需要一个运算符类... sqlalchemy.exc.ProgrammingError: (psycopg2.errors.UndefinedObject) data type integer has no default operator class for access method "gist" HINT: You must specify an operator class for the index or define a default operator class for the data type. - Zaffer

Answer 4

如果由于某种原因无法更改表模式并且需要保留两个时间行，则可以在约束范围内构建范围，例如：

CREATE TABLE reservations (
    datefrom timestamp,
    dateto timestamp,
    EXCLUDE USING gist (tsrange(datefrom, dateto) WITH &&)
);

在这种情况下，我使用了tsrange来处理时间戳类型，但还有其他你可以使用的方法 - 请查看https://www.postgresql.org/docs/current/rangetypes.html中的文档。

- setnoset

Answer 5

-- Implementation of a CONSTRAINT on non-overlapping datetime ranges
-- , using the Postgres rulesystem.
-- This mechanism should work for 8.4, without needing triggers.(tested on 9.0)
-- We need a shadow-table for the rangesonly 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

Answer 6

您是否真的想要一个像标题中提到的 CHECK 约束？这是不可能的，因为 CHECK 约束只能一次处理一行。不过，可能可以使用触发器来实现这个功能...

- Karl Bartel

---

任何限制数据的方式都足够了。我只是（错误地！）假设它会是一个CHECK... - Michael