Ограничение на перекрытие даты в Postgres

Ответ 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 и btree_gist, доступном как расширение в 9.1 CREATE EXTENSION btree_gist;

nb: если у вас нет нулевой метки времени, вам не нужно использовать временное расширение, которое вы могли бы использовать с помощью метода box, как указано в моем вопросе.

Ответ 2

Это сложная проблема, поскольку ограничения могут ссылаться только на "текущую строку" и не содержать подзапросов. (в противном случае тривиальное решение заключалось бы в том, чтобы добавить некоторый подкасстер NOT EXISTS() в проверку)

Ограничение проверки, указанное как ограничение столбца, должно ссылаться только на это значение столбца, тогда как выражение, появляющееся в ограничении таблицы, может ссылаться на несколько столбцов.

В настоящее время выражения CHECK не могут содержать подзапросы и не ссылаются на переменные, отличные от столбцов текущей строки.

Популярные рабочие элементы: используйте функцию триггера, которая выполняет грязную работу (или использует систему правил, которая устарела большинством людей)

Поскольку большинство людей предпочитают триггеры, я буду переписывать здесь взломанную систему правил (у него нет дополнительного ключевого элемента "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;

Ответ 3

В современных версиях postgres (я тестировал его в 9.6, но я предполагаю, что он работает в >= 9.2), вы можете использовать функцию сборки tstzrange(), как упоминалось в некоторых других комментариях. Значения Null будут считаться положительными или отрицательными бесконечностями по умолчанию, и больше не требуется явно запрет CHECK (если вы в порядке, чтобы проверка была только <=, и диапазон может начинаться и заканчиваться с той же датой). Требуется только расширение btree_gist:

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 &&
    )
);

Ответ 4

Как создать уникальное ограничение для группы столбцов:

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

в вашем случае вам нужно будет ALTER TABLE, если таблица уже существует, проверьте документацию, которая вам будет полезна:
- Ограничения DDL
- Таблица ALTER