exclusion constraint with overlapping timestamps

Поиск
Список
Период
Сортировка
От A.M.
Тема exclusion constraint with overlapping timestamps
Дата
Msg-id 49016DD2-3D4A-4EA6-8C57-EE2116053AB9@themactionfaction.com
обсуждение исходный текст
Ответы Re: exclusion constraint with overlapping timestamps  (Jeff Davis <pgsql@j-davis.com>)
Список pgsql-general
I am experimenting with exclusion constraints via Depesz's excellent introduction here:
http://www.depesz.com/index.php/2010/01/03/waiting-for-8-5-exclusion-constraints/

In the example, he uses non-overlapping (day) dates for hotel booking. In my case, I would like to use the same
datatypebut allow for timestamps to overlap on the boundaries, so that I can store a continuous timeline of state. 

CREATE TABLE test.x
(
    validfrom TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
    validto TIMESTAMP WITH TIME ZONE,
    CHECK(validfrom < COALESCE(validto,'infinity'::timestamptz)),
    CONSTRAINT overlapping_validity EXCLUDE USING GIST(
        box(point(extract(epoch FROM validfrom AT TIME ZONE 'UTC'),0),
            point(extract(epoch FROM validto AT TIME ZONE 'UTC'),1))
        WITH &&
    )
);

INSERT INTO test.x(validfrom,validto) VALUES ('2010-08-08 10:00:00 UTC','2010-08-08 11:00:00 UTC'); --success
INSERT INTO test.x(validfrom,validto) VALUES ('2010-08-08 11:00:00 UTC','2010-08-08 12:00:00 UTC'); --failure, but
shouldsucceed in my design 
INSERT INTO test.x(validfrom,validto) VALUES ('2010-08-08 10:30:00 UTC','2010-08-08 11:00:00 UTC'); --proper failure

I considered adding a fudge factor to the box values, but that feels prone to failure in edge cases (why can't I have a
valuethat is valid for one second?). 

Do I need to write a new box operator which checks ignores overlap at the edges or is a better way to accomplish this?
Thanks.

Cheers,
M

В списке pgsql-general по дате отправления:

Предыдущее
От: "Joshua J. Kugler"
Дата:
Сообщение: Re: "private" installation of postgres
Следующее
От: Sam Nelson
Дата:
Сообщение: Re: Memory Errors