pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedHow to check date-interval constraints
3+ messages / 3 participants
[nested] [flat]
* How to check date-interval constraints
@ 2006-03-03 03:28 Andreas <maps.on@gmx.net>
0 siblings, 1 reply; 3+ messages in thread
From: Andreas @ 2006-03-03 03:28 UTC (permalink / raw)
To: pgsql-sql
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.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: How to check date-interval constraints
@ 2006-03-03 05:13 Michael Fuhr <mike@fuhr.org>
parent: Andreas <maps.on@gmx.net>
0 siblings, 1 reply; 3+ messages in thread
From: Michael Fuhr @ 2006-03-03 05:13 UTC (permalink / raw)
To: Andreas <maps.on@gmx.net>; +Cc: pgsql-sql
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 integer AS $$
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
DEFAULT FOR 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 date NOT 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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: How to check date-interval constraints
@ 2006-03-03 05:25 Michael Glaesemann <grzm@myrealbox.com>
parent: Michael Fuhr <mike@fuhr.org>
0 siblings, 0 replies; 3+ messages in thread
From: Michael Glaesemann @ 2006-03-03 05:25 UTC (permalink / raw)
To: Michael Fuhr <mike@fuhr.org>; +Cc: Andreas <maps.on@gmx.net>; pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2006-03-03 05:25 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-03-03 03:28 How to check date-interval constraints Andreas <maps.on@gmx.net>
2006-03-03 05:13 ` Michael Fuhr <mike@fuhr.org>
2006-03-03 05:25 ` Michael Glaesemann <grzm@myrealbox.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox