From: Andrew Milne <amilne@solutioninc.com>
To: pgsql-sql@postgresql.org
Subject: Unique Constraint Based on Date Range
Date: Thu, 18 Sep 2003 15:42:07 -0300
Message-ID: <3F69FC7F.1040103@solutioninc.com> (raw)
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: amilne@solutioninc.com
Subject: Re: Unique Constraint Based on Date Range
In-Reply-To: <3F69FC7F.1040103@solutioninc.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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