pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedUnique Constraint Based on Date Range
3+ messages / 3 participants
[nested] [flat]
* Unique Constraint Based on Date Range
@ 2003-09-18 18:42 Andrew Milne <amilne@solutioninc.com>
0 siblings, 1 reply; 3+ messages in thread
From: Andrew Milne @ 2003-09-18 18:42 UTC (permalink / raw)
To: pgsql-sql
I'm looking to apply a unique constraint to a table such that field A
must be unique based on the date range from Field B to Field C.
This is for a rate based service model whereby, for example, $5 is worth
1 hour of Internet access. But next week, because of increased
competition, $5 is worth 2 hours of Internet access. I want to maintain
a history what $5 bought during a specific period of time.
create table rates (
effective_date AS timestamp,
expiry_date AS timestamp,
cost AS numeric (12,2),
access_time AS integer (in minutes)
);
So for a given cost, there may not be a record where the effective date
of one record overlaps the expiry date of another (and vice versa).
Example record set (effective date, expiry date, cost, access_time):
2003-01-01 | 2003-01-15 | 5.00 | 60
2003-01-15 | infinity | 5.00 | 120
2003-01-01 | infinity | 1.00 | 10
An attempt to insert another 5.00 rate effective now would fail, because
a 5.00 rate exists that doesn't expire (i.e. the expiry date would have
to be updated to the effective date of the new record minus 1 second).
I can enforce this from the front end, but a db constraint would be great.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Unique Constraint Based on Date Range
@ 2003-09-20 22:55 Kevin Houle <kevin@houle.org>
parent: Andrew Milne <amilne@solutioninc.com>
0 siblings, 1 reply; 3+ messages in thread
From: Kevin Houle @ 2003-09-20 22:55 UTC (permalink / raw)
To: Andrew Milne <amilne@solutioninc.com>
Andrew Milne wrote:
...
> create table rates (
> effective_date AS timestamp,
> expiry_date AS timestamp,
> cost AS numeric (12,2),
> access_time AS integer (in minutes)
> );
>
> So for a given cost, there may not be a record where the effective date
> of one record overlaps the expiry date of another (and vice versa).
>
> Example record set (effective date, expiry date, cost, access_time):
>
> 2003-01-01 | 2003-01-15 | 5.00 | 60
> 2003-01-15 | infinity | 5.00 | 120
> 2003-01-01 | infinity | 1.00 | 10
>
> An attempt to insert another 5.00 rate effective now would fail, because
> a 5.00 rate exists that doesn't expire (i.e. the expiry date would have
> to be updated to the effective date of the new record minus 1 second).
>
> I can enforce this from the front end, but a db constraint would be great.
I don't know that a CHECK constraint would allow you to do this.
But, you could create a function to perform the check, and fire
a trigger on INSERT or UPDATE to execute the function. For example,
something like this might do the trick.
CREATE FUNCTION "check_record" () RETURNS TRIGGER AS '
DECLARE
result RECORD;
BEGIN
SELECT INTO result * FROM table_rates WHERE
effective_date >= NEW.effective_date AND
expiry_date <= NEW.expiry_date AND
cost = NEW.cost;
IF FOUND THEN
RAISE EXCEPTION ''record overlaps with existing record'';
END IF;
RETURN NEW;
END; ' LANGUAGE 'plpgsql';
CREATE TRIGGER "tg_check_record"
BEFORE INSERT OR UPDATE ON table_rates
FOR EACH ROW EXECUTE PROCEDURE "check_record" ();
Kevin
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Unique Constraint Based on Date Range
@ 2003-09-22 09:14 Manfred Koizar <mkoi-pg@aon.at>
parent: Kevin Houle <kevin@houle.org>
0 siblings, 0 replies; 3+ messages in thread
From: Manfred Koizar @ 2003-09-22 09:14 UTC (permalink / raw)
To: Kevin Houle <kevin@houle.org>; +Cc: Andrew Milne <amilne@solutioninc.com>; pgsql-sql
On Sat, 20 Sep 2003 18:55:34 -0400, Kevin Houle <kevin@houle.org>
wrote:
> SELECT INTO result * FROM table_rates WHERE
> effective_date >= NEW.effective_date AND
> expiry_date <= NEW.expiry_date AND
> cost = NEW.cost;
> IF FOUND THEN
> RAISE EXCEPTION ''record overlaps with existing record'';
> END IF;
This only catches complete inclusion of an old range in the new one.
new b---------------------e
old b--------------e
Try
WHERE effective_date < NEW.expiry_date
AND expiry_date > NEW.effective_date
which also detects
new b--------------------e
old b-------------------e
old b------------------e
old b--------------------------------e
Servus
Manfred
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2003-09-22 09:14 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-09-18 18:42 Unique Constraint Based on Date Range Andrew Milne <amilne@solutioninc.com>
2003-09-20 22:55 ` Kevin Houle <kevin@houle.org>
2003-09-22 09:14 ` Manfred Koizar <mkoi-pg@aon.at>
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