Date: Thu, 2 Mar 2006 22:13:30 -0700
From: Michael Fuhr <mike@fuhr.org>
To: Andreas <maps.on@gmx.net>
Cc: pgsql-sql@postgresql.org
Subject: Re: How to check date-interval constraints
Message-ID: <20060303051330.GA34533@winnie.fuhr.org>
References: <4407B7C1.7030101@gmx.net>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <4407B7C1.7030101@gmx.net>

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?  Something like the following:

  CREATE TYPE drange AS (
      dstart  date,
      dstop   date
  );
  
  CREATE FUNCTION drange_cmp(drange, drange) RETURNS integer AS $$
  BEGIN
      RETURN CASE WHEN $1.dstop < $2.dstart THEN -1
                  WHEN $1.dstart > $2.dstop THEN 1
                  ELSE 0
             END;
  END;
  $$ LANGUAGE plpgsql IMMUTABLE STRICT;
  
  CREATE OPERATOR CLASS drange_ops
    DEFAULT FOR TYPE drange USING btree AS
    FUNCTION 1 drange_cmp(drange, drange);

Here's an example that includes your additional constraint of the
range being non-overlapping only for rows with the same type-column:

  CREATE TABLE foo (
      id     integer PRIMARY KEY,
      ftype  integer NOT NULL,
      fstart date NOT NULL,
      fstop  date NOT NULL CHECK (fstop >= fstart)
  );
  
  CREATE UNIQUE INDEX foo_uniq_idx ON foo (ftype, (row(fstart, fstop)::drange));
  
  INSERT INTO foo VALUES (1, 1, '2006-01-01', '2006-01-31'); -- ok
  INSERT INTO foo VALUES (2, 1, '2006-02-01', '2006-02-28'); -- ok
  INSERT INTO foo VALUES (3, 1, '2006-01-10', '2006-01-20'); -- bad
  INSERT INTO foo VALUES (4, 1, '2006-01-21', '2006-02-10'); -- bad
  INSERT INTO foo VALUES (5, 2, '2006-01-10', '2006-01-20'); -- ok
  INSERT INTO foo VALUES (6, 2, '2006-01-21', '2006-02-10'); -- ok

I've done only minimal testing with this but so far it seems to
work, even with concurrent transactions.  However, I'm not sure
this is the best way to approach the problem; if it's flawed then
hopefully somebody will point out why and maybe suggest something
else.

-- 
Michael Fuhr