Re: UNIQUEness and time interval

On Oct 4, 4:45 am, Nicolas Boullis <nicolas(dot)boul(dot)(dot)(dot)(at)ecp(dot)fr> wrote:
> I'd like to define a table with a "name", a "start_date" and a
> "stop_date" columns, with a constraint that ensures that 2 records with
> ovelapping dates don't share the same name. Is there a way to define
> such a constraint?

CREATE TABLE T(
NAME TEXT
,START_DATE DATE
,STOP_DATE DATE
);

CREATE OR REPLACE FUNCTION F() RETURNS TRIGGER AS $$
BEGIN
IF EXISTS (
SELECT 1
FROM T
WHERE NAME = NEW.NAME
AND (START_DATE, STOP_DATE)
OVERLAPS
(NEW.START_DATE, NEW.STOP_DATE)
OR STOP_DATE = NEW.START_DATE
)
THEN
RAISE EXCEPTION 'WHATCHA DOIN'' FOO';
RETURN NULL;
END IF;
RETURN NEW;
END;
$$ LANGUAGE PLPGSQL;

CREATE TRIGGER T_T
BEFORE INSERT ON T
FOR EACH ROW
EXECUTE PROCEDURE F();

INSERT INTO T VALUES ('FOO','2007-1-1','2007-1-3'); -- OK

INSERT INTO T VALUES ('FOO','2007-1-4','2007-1-6'); -- OK

INSERT INTO T VALUES ('FOO','2007-1-6','2007-1-8'); -- WILL BARF