Re: CONSTRAINT does not show when applying a EXCLUDE constraint

SOLVED: I should use the "CREATE UNIQUE INDEX ... ON ..." command, on an
existing table. This cannot be done inline within the CREATE TABLE command.

Thanks all, for your help.

Rob

2010/12/28 InterRob <rob(dot)marjot(at)gmail(dot)com>

> 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(at)sss(dot)pgh(dot)pa(dot)us>
>
> 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
>>
>>
>