Обсуждение: How to check date-interval constraints
Hi, I'd like to have a table that looks like this: my_option ( id serial primary key, myvalue double, valid_start timestamp, valid_stop timestamp ); I want to store values that are only valid in a given start-stop-interval so I could find a date-specific value for NOW() or some other given date. select myvalue from my_option where somedate between valid_start and valid_stop; How can I have a constraint, that prohibits nesting or overlapping intervals? 1 7 2006-1-1 2006-1-31 2 9 2006-2-1 2006-2-28 OK 3 5 2006-1-10 2006-1-20 BAD lies within line 1 4 3 2006-1-20 2006-2-10 BAD starts within line 1 and ends in line 2 To make it even more interesting, it'd be nice to add a type-column so I could ask: select myvalue from my_option where now() between valid_start and valid_stop AND mytype=42; Then interval should ONLY not overlap with other intervals of the SAME type.
On Fri, Mar 03, 2006 at 04:28:01AM +0100, Andreas wrote:
> How can I have a constraint, that prohibits nesting or overlapping
> intervals?
>
> 1 7 2006-1-1 2006-1-31
> 2 9 2006-2-1 2006-2-28 OK
> 3 5 2006-1-10 2006-1-20 BAD lies within line 1
> 4 3 2006-1-20 2006-2-10 BAD starts within line 1
> and ends in line 2
This is just a brainstorm, but what about creating a composite type,
a comparison function, and an operator class, then declaring a
unique index on that composite type? Something like the following:
CREATE TYPE drange AS ( dstart date, dstop date ); CREATE FUNCTION drange_cmp(drange, drange) RETURNS
integerAS $$ BEGIN RETURN CASE WHEN $1.dstop < $2.dstart THEN -1 WHEN $1.dstart > $2.dstop THEN 1
ELSE 0 END; END; $$ LANGUAGE plpgsql IMMUTABLE STRICT; CREATE OPERATOR CLASS drange_ops
DEFAULTFOR TYPE drange USING btree AS FUNCTION 1 drange_cmp(drange, drange);
Here's an example that includes your additional constraint of the
range being non-overlapping only for rows with the same type-column:
CREATE TABLE foo ( id integer PRIMARY KEY, ftype integer NOT NULL, fstart date NOT NULL, fstop
dateNOT NULL CHECK (fstop >= fstart) ); CREATE UNIQUE INDEX foo_uniq_idx ON foo (ftype, (row(fstart, fstop)::drange));
INSERT INTO foo VALUES (1, 1, '2006-01-01', '2006-01-31'); -- ok INSERT INTO foo VALUES (2, 1, '2006-02-01',
'2006-02-28');-- ok INSERT INTO foo VALUES (3, 1, '2006-01-10', '2006-01-20'); -- bad INSERT INTO foo VALUES (4, 1,
'2006-01-21','2006-02-10'); -- bad INSERT INTO foo VALUES (5, 2, '2006-01-10', '2006-01-20'); -- ok INSERT INTO foo
VALUES(6, 2, '2006-01-21', '2006-02-10'); -- ok
I've done only minimal testing with this but so far it seems to
work, even with concurrent transactions. However, I'm not sure
this is the best way to approach the problem; if it's flawed then
hopefully somebody will point out why and maybe suggest something
else.
--
Michael Fuhr
On Mar 3, 2006, at 14:13 , Michael Fuhr wrote: > On Fri, Mar 03, 2006 at 04:28:01AM +0100, Andreas wrote: >> How can I have a constraint, that prohibits nesting or overlapping >> intervals? >> >> 1 7 2006-1-1 2006-1-31 >> 2 9 2006-2-1 2006-2-28 OK >> 3 5 2006-1-10 2006-1-20 BAD lies within line 1 >> 4 3 2006-1-20 2006-2-10 BAD starts within >> line 1 >> and ends in line 2 > > This is just a brainstorm, but what about creating a composite type, > a comparison function, and an operator class, then declaring a > unique index on that composite type? Another way is presented in Snodgrass' "Developing Time-Oriented Database Applications in SQL", out of print but available as a PDF download from his website: http://www.cs.arizona.edu/people/rts/tdbbook.pdf You'll need to use CREATE CONSTRAINT TRIGGER rather than just CREATE TRIGGER to apply the constraints you're looking for, as often you'll need to wrap a multi-statement update in a transaction to ensure integrity. Michael Glaesemann grzm myrealbox com