From: | InterRob <rob(dot)marjot(at)gmail(dot)com> |
---|---|
To: | pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: CONSTRAINT does not show when applying a EXCLUDE constraint |
Date: | 2010-12-27 20:50:57 |
Message-ID: | AANLkTimb3LVd80KtnLgsDa3u5EF7PZ9X6RHOJCur3L80@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Sorry: that is because the major part of my E-mail got chopped off...
Herewith I am resending it:
-----------------------------------------------
Dear list,
To rule out any double combination of two identifiers, in any order, I
applied the following constraint to a table:
CREATE TABLE test(
object_id INTEGER,
subject_id INTEGER,
CONSTRAINT "EXCL_double_combi" EXCLUDE USING btree (imm_LEAST(subject_id,
object_id) WITH =, imm_GREATEST(subject_id, object_id) WITH =)
);
The imm_xxxxx functions are IMMUTABLE wrappers around LEAST() and GREATEST()
respectively, in order to get the index to work...
pgsql Command "\d test" produces the following:
Table "public.test"
Column | Type | Modifiers
------------+---------+-----------
object_id | integer |
subject_id | integer |
Indexes:
"EXCL_double_combi" EXCLUDE USING btree (imm_least(subject_id,
object_id) WITH =, imm_greatest(subject_id, object_id) WITH =)
As I read it, an indexed in indeed being maintained, however it is not
regarded a CONSTRAINT... Yet, the set-up works as like a charm :)) This
online example shows a different summary:
http://www.depesz.com/index.php/2010/01/03/waiting-for-8-5-exclusion-constraints/
Would this be an earlier / older version of PostgreSQL? I'm running 9.0.2,
on Windows 7
By the way: has any one experience using EXCLUDE constraint inside
Enterprise Architect? Since this is a pretty new database feature, it seems
not yet supported by EA?
Looking forward to any of you sharing your experiences,
kind regards,
Rob
-----------------------------------------------
2010/12/27 Richard Broersma <richard(dot)broersma(at)gmail(dot)com>
> On Mon, Dec 27, 2010 at 8:31 AM, Rob Marjot <rob(at)marjot-multisoft(dot)com>
> wrote:
> > Dear list,
> > To rule out any double combination of two identifiers, in any order, I
> > applied the following constraint to a table:
> > CREATE TABLE test(object_id INTEGER, subject_id INTEGER, CONSTRAINT
>
> I don't see the definition of your constraint.
>
> --
> Regards,
> Richard Broersma Jr.
>
>
From | Date | Subject | |
---|---|---|---|
Next Message | Richard Broersma | 2010-12-27 21:02:03 | Re: CONSTRAINT does not show when applying a EXCLUDE constraint |
Previous Message | Richard Broersma | 2010-12-27 20:37:55 | Re: CONSTRAINT does not show when applying a EXCLUDE constraint |