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.
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
> 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,
> 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?
> 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>
>> >> 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
>> "foo_pkey" PRIMARY KEY, btree (f1)
>> 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
pgsql-general by date
|Next:||From: aaliya zarrin||Date: 2010-12-28 10:14:50|
|Subject: Re: Startup Process Initiated by init proc (Unix)|
|Previous:||From: el dorado||Date: 2010-12-28 10:02:20|
|Subject: PowerDesigner 15|