Re: CONSTRAINT does not show when applying a EXCLUDE constraint

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Richard Broersma <richard(dot)broersma(at)gmail(dot)com>
Cc: rob(at)marjot-multisoft(dot)com, pgsql-general <pgsql-general(at)postgresql(dot)org>
Subject: Re: CONSTRAINT does not show when applying a EXCLUDE constraint
Date: 2010-12-27 21:13:39
Message-ID: 5582.1293484419@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Richard Broersma <richard(dot)broersma(at)gmail(dot)com> writes:
> On Mon, Dec 27, 2010 at 12:50 PM, InterRob <rob(dot)marjot(at)gmail(dot)com> wrote:
>> 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 =)

> I see. Since exclusion constraints is a rather new feature, psql may
> need some tweaking to report these constraints as constraints

No, the behavior is entirely intentional. Compare what it's always been
for primary keys:

regression=# create table foo (f1 int primary key);
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "foo_pkey" for table "foo"
CREATE TABLE
regression=# \d foo
Table "public.foo"
Column | Type | Modifiers
--------+---------+-----------
f1 | integer | not null
Indexes:
"foo_pkey" PRIMARY KEY, btree (f1)

regression=#

BTW, I fail to see the point of using EXCLUDE in this particular way.
You'd get the same results, more efficiently, with a plain UNIQUE
constraint on the two function expressions. EXCLUDE is really only
interesting for cases where the behavior you want doesn't conform to
btree semantics.

regards, tom lane

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Michael Satterwhite 2010-12-27 21:14:03 Re: Help with trigger
Previous Message Richard Broersma 2010-12-27 21:02:03 Re: CONSTRAINT does not show when applying a EXCLUDE constraint