Re: Unique Constraint Based on Date Range

Поиск
Список
Период
Сортировка
Искать

Re: Unique Constraint Based on Date Range

От:
Manfred Koizar <mkoi-pg@aon.at>
Дата:
On Sat, 20 Sep 2003 18:55:34 -0400, Kevin Houle 
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

TryWHERE 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

ServusManfred

Unique Constraint Based on Date Range

От:
Andrew Milne <amilne@solutioninc.com>
Дата:
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.

Re: Unique Constraint Based on Date Range

От:
Kevin Houle <kevin@houle.org>
Дата:
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
FAQ