Re: How to check date-interval constraints - Mailing list pgsql-sql

From Michael Glaesemann
Subject Re: How to check date-interval constraints
Date
Msg-id 1B741D25-BFC8-4150-81C7-2705AF50D21B@myrealbox.com
Whole thread Raw
In response to Re: How to check date-interval constraints  (Michael Fuhr <mike@fuhr.org>)
List pgsql-sql
On Mar 3, 2006, at 14:13 , Michael Fuhr wrote:

> On Fri, Mar 03, 2006 at 04:28:01AM +0100, Andreas wrote:
>> How can I have a constraint, that prohibits nesting or overlapping
>> intervals?
>>
>> 1    7    2006-1-1     2006-1-31
>> 2    9    2006-2-1     2006-2-28               OK
>> 3    5    2006-1-10   2006-1-20               BAD  lies within line 1
>> 4    3    2006-1-20   2006-2-10               BAD  starts within  
>> line 1
>> and ends in line 2
>
> This is just a brainstorm, but what about creating a composite type,
> a comparison function, and an operator class, then declaring a
> unique index on that composite type?

Another way is presented in Snodgrass' "Developing Time-Oriented  
Database Applications in SQL", out of print but available as a PDF  
download from his website:

http://www.cs.arizona.edu/people/rts/tdbbook.pdf

You'll need to use CREATE CONSTRAINT TRIGGER rather than just CREATE  
TRIGGER to apply the constraints you're looking for, as often you'll  
need to wrap a multi-statement update in a transaction to ensure  
integrity.

Michael Glaesemann
grzm myrealbox com





pgsql-sql by date:

Previous
From: Michael Fuhr
Date:
Subject: Re: How to check date-interval constraints
Next
From: Bryce Nesbitt
Date:
Subject: Sequential scan where Index scan expected.