Re: Need help writing exclusion constraint

On Sat, 2011-01-15 at 15:07 -0500, Daniel Popowich wrote:
> Constraint expressions can only be simple boolean expressions, so can
> refer only to the column(s) of the current row you're
> inserting/updating,

Exclusion Constraints are a new feature in 9.0:

http://www.postgresql.org/docs/9.0/static/ddl-constraints.html#DDL-CONSTRAINTS-EXCLUSION
http://www.postgresql.org/docs/9.0/static/sql-createtable.html#SQL-CREATETABLE-EXCLUDE

They allow you to constrain across rows, much like UNIQUE (in fact, the
constraints that can be expressed by an exclusion constraint are a
superset of the constraints that can be expressed by UNIQUE).

> so to refer to other records (which you'll need to
> do to compare destination_ids) you need to create a
> function...something along the lines of this:
>

...

> ALTER TABLE event ADD CONSTRAINT event_overlap
> CHECK(overlap_at_dest(destination_id, starts, ends));

As Tomas said, that's an unsafe thing to do. I do not recommend using a
table-reading function in a check constraint.

Regards,
Jeff Davis