From: | Jeff Davis <pgsql(at)j-davis(dot)com> |
---|---|
To: | Daniel Popowich <danielpopowich(at)gmail(dot)com> |
Cc: | Matthew Wilson <matt(at)tplus1(dot)com>, pgsql-general(at)postgresql(dot)org |
Subject: | Re: Need help writing exclusion constraint |
Date: | 2011-01-18 19:29:09 |
Message-ID: | 1295378949.22206.19.camel@jdavis-ux.asterdata.local |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Jeff Davis | 2011-01-18 19:31:01 | Re: Need help writing exclusion constraint |
Previous Message | Alban Hertroys | 2011-01-18 19:28:07 | Re: Getting a sample data set. |