Re: CONSTRAINT does not show when applying a EXCLUDE constraint

Dear Tom,

Thanks for your hints; it is indeed funny (as mentioned by Guillaume) how indexes and constraints are being mixed up -- mentioned either in the Indexes or in Constraints department. Pgsql and PgAdmin make different choices here. And well, given their implementation these choices can both be understood...

Anyway, your hint using UNIQUE is what I thought of myself earlier as well. Yet, I don't get it to work:

CREATE TABLE unique_test(subject_id INTEGER, object_id INTEGER, UNIQUE(imm_least(subject_id, object_id), imm_greatest(subject_id, object_id)));

The above command generates a syntax error at the postion of the first parenthesis of the first function call inside the UNIQUE expression... From the PG syntax reference I understand only column expressions are allowed... It seems to me that CASE is not supported either.

Am I missing something?

Rob

2010/12/27 Tom Lane <tgl@sss.pgh.pa.us>

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