From: | "A(dot)M(dot)" <agentm(at)themactionfaction(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | exclusion constraint with overlapping timestamps |
Date: | 2010-09-08 19:48:57 |
Message-ID: | 49016DD2-3D4A-4EA6-8C57-EE2116053AB9@themactionfaction.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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 datatype but 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 should succeed 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 value that 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
From | Date | Subject | |
---|---|---|---|
Next Message | Sam Nelson | 2010-09-08 20:03:38 | Re: Memory Errors |
Previous Message | Joshua J. Kugler | 2010-09-08 19:39:37 | Re: "private" installation of postgres |