Re: How to check date-interval constraints

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