Re: Best way to prevent overlapping dates

On May 25, 2007, at 3:22 , Andrus wrote:

> CREATE TRIGGER puhkus_sequenced_trigger BEFORE INSERT OR UPDATE ON
> puhkus
> FOR EACH ROW EXECUTE PROCEDURE puhkus_sequenced_pkey();

You can also use CREATE CONSTRAINT TRIGGER, which allows you to have
deferrable constraints. This is useful if statements within a
transaction may temporarily put the database in a state where it
violates the constraint: the constraint will be called at the end of
the transaction to make sure integrity is maintained.

http://www.postgresql.org/docs/8.2/interactive/sql-createconstraint.html

> create table puhkus (palgus date, plopp date);
>
> alter table puhkus add check
> (NOT EXISTS ( SELECT *
> FROM puhkus AS I1
> WHERE 1 < (SELECT COUNT(*)
> FROM puhkus AS I2
> WHERE doverlaps(i1.palgus, i1.plopp, i2.palgus, i2.plopp)
> ) ));
>
> but got error
>
> ERROR: cannot use subquery in check constraint

Right. As the error says, subqueries in CHECK constraints is not
implemented in PostgreSQL.

Michael Glaesemann
grzm seespotcode net