From: Michael Glaesemann <grzm@myrealbox.com>
To: Michael Fuhr <mike@fuhr.org>
Cc: Andreas <maps.on@gmx.net>
Cc: pgsql-sql@postgresql.org
Subject: Re: How to check date-interval constraints
Date: Fri, 3 Mar 2006 14:25:47 +0900
Message-ID: <1B741D25-BFC8-4150-81C7-2705AF50D21B@myrealbox.com> (raw)
In-Reply-To: <20060303051330.GA34533@winnie.fuhr.org>
References: <4407B7C1.7030101@gmx.net>
<20060303051330.GA34533@winnie.fuhr.org>
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
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: grzm@myrealbox.com, mike@fuhr.org, maps.on@gmx.net
Subject: Re: How to check date-interval constraints
In-Reply-To: <1B741D25-BFC8-4150-81C7-2705AF50D21B@myrealbox.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