Unique Constraint Based on Date Range
От | Andrew Milne |
---|---|
Тема | Unique Constraint Based on Date Range |
Дата | |
Msg-id | 3F69FC7F.1040103@solutioninc.com обсуждение исходный текст |
Ответы |
Re: Unique Constraint Based on Date Range
|
Список | 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_timeAS 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.
В списке pgsql-sql по дате отправления: