X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id 1B1ED9DCDA2 for ; Fri, 3 Mar 2006 01:25:58 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 64779-01 for ; Fri, 3 Mar 2006 01:25:54 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from smtp-send.myrealbox.com (smtp-send.myrealbox.com [151.155.5.143]) by postgresql.org (Postfix) with ESMTP id BC5169DCD94 for ; Fri, 3 Mar 2006 01:25:53 -0400 (AST) Received: from [172.16.1.180] grzm [61.197.227.146] by smtp-send.myrealbox.com with NetMail SMTP Agent $Revision: 1.6 $ on Linux; Thu, 02 Mar 2006 22:25:50 -0700 In-Reply-To: <20060303051330.GA34533@winnie.fuhr.org> References: <4407B7C1.7030101@gmx.net> <20060303051330.GA34533@winnie.fuhr.org> Mime-Version: 1.0 (Apple Message framework v746.2) Content-Type: text/plain; charset=US-ASCII; delsp=yes; format=flowed Message-Id: <1B741D25-BFC8-4150-81C7-2705AF50D21B@myrealbox.com> Cc: Andreas , pgsql-sql@postgresql.org Content-Transfer-Encoding: 7bit From: Michael Glaesemann Subject: Re: How to check date-interval constraints Date: Fri, 3 Mar 2006 14:25:47 +0900 To: Michael Fuhr X-Mailer: Apple Mail (2.746.2) X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.438 required=5 tests=[AWL=0.438] X-Spam-Score: 0.438 X-Spam-Level: X-Archive-Number: 200603/57 X-Sequence-Number: 24403 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