Message-ID: <3F69FC7F.1040103@solutioninc.com>
Date: Thu, 18 Sep 2003 15:42:07 -0300
From: Andrew Milne <amilne@solutioninc.com>
User-Agent: Mozilla/5.0 (Windows; U; Win98; en-US;
	rv:1.4) Gecko/20030624 Netscape/7.1 (ax)
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Subject: Unique Constraint Based on Date Range
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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.