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
|
Список | 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 по дате отправления: