Postgres дата, перекрывающая ограничение

У меня есть такая таблица:

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/

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

 vyegorov16 мая 2012 г., 13:03
Вы должны пойти наCREATE CONSTRAINT TRIGGER и написать конкретную функцию для выполнения вашей проверки.
 yokoloko16 мая 2012 г., 12:20
да, именно это слово, которое я искал
 yokoloko18 мая 2012 г., 09:26
Я думал, что была проблема с транзакциями и триггером, триггеры не знают о других транзакциях, запущенных в то же время.
 wildplasser16 мая 2012 г., 12:12
Вы хотите запретить перекрывающиеся интервалы?
 wildplasser17 мая 2012 г., 23:01
Взлом "карта-интервал в 2D-геометрию" также был размещен здесь. (округление до плавания воняет) Но, честно говоря: создание триггера (или правила + канарейка, как в моем вкладе) - это путь. Вы даже можете поймать случай end_date IS NULL с этим. И сравнение точное. ПРИМЕЧАНИЕ: не используйте правила, если вы не знаете, что делаете; -]

Ответы на вопрос(4)

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

A check constraint specified as a column constraint should reference that column's value only, while an expression appearing in a table constraint can reference multiple columns.

Currently, CHECK expressions cannot contain subqueries nor refer to variables other than columns of the current row.

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

Поскольку большинство людей предпочитают триггеры, я здесь сообщу о взломе системы правил ... (у него нет дополнительного ключевого элемента "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;
 yokoloko17 мая 2012 г., 22:41
Я думаю, что можно что-то сделать с исключением ограничений. Не уверен, хотя, посмотрите на мою правку.

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

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

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

 16 мая 2012 г., 12:13
pg-8.1 довольно старая версия.
 yokoloko16 мая 2012 г., 12:21
хм, это не совсем то, что я ищу. Я ищу что-то, чтобы запретить перекрывающиеся интервалы. но спасибо
Решение Вопроса

Хорошо, я закончил тем, что сделал это:

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;

Примечание: если у вас нет нулевой метки времени, нет необходимости использовать временное расширение, вы можете использовать метод box, как указано в моем вопросе.

 18 окт. 2012 г., 01:54
С выпуском 9.2 был добавлен тип диапазона, включая диапазон дат, который упрощает ваше согласие и всю эту проблемную область.postgresql.org/docs/devel/static/rangetypes.html
 31 янв. 2013 г., 11:05
Вы могли бы использоватьCOALESCE(to_ts, 'infinity'::timestamptz) вместоCASE WHEN сделать это меньшеUPPERCASE WORD SALAD
 21 мая 2012 г., 17:51
+1 Идеальный вариант использования дляexclusion constraint (новое в Postgres 9.0). ВместоCASE Заявление вы могли бы определить столбецto_ts TIMESTAMPTZ NOT NULL DEFAULT 'infinity', И то же самое с'-infinity' заfrom_ts.
 05 июл. 2013 г., 19:58
Более того, типы диапазона 9,2 рассматривают NULL как +/- бесконечность. Так что вместоCASE или дажеCOALESCE, вы получаете использоватьto_ts непосредственно.
 05 июл. 2013 г., 19:32
Я не пробовал это, но похоже, что это будет работать в 9.2 без необходимости устанавливать временное расширение путем заменыperiod() функция сtstzrange().

В последних версиях Postgres (я тестировал его в 9.6, но я предполагаю, что он работает в> 9.2) вы можете использовать функцию build intstzrange() как уже упоминалось в некоторых других комментариях. По умолчанию нулевые значения будут рассматриваться как положительная или отрицательная бесконечность, и тогда ограничение 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 &&
    )
);

Ваш ответ на вопрос