Re: how to assign primary key or unique constraint to pre-existing index?

Gunther Schadow wrote:
> Hi, this is probably a FAQ: is there any way to tell pgsql to
> use a specific preexising index for PK or UNIQUE constraints
> instead of creating a new one?
>

I don't think so...
It is quite puzzling to me, why it is allowed to create duplicated indexes on the same keys to begin with...:-(

The way around it I am using is - forget about pk/unique constraints and just go with indexes:

create table a (x int unique)
is IDENTICAL to
create table a (x int);
create unique index a_x_key on a(x);

and:

create table a (x int primary key);
is not exactly the same, but functionally IDENTICAL to
create table a (x int not null);
create unique index a_pkey on a(x);

The only difference between the last two cases, as far as I can tell is that the former schema is described
by '\d' as

           Table "a"
  Column |  Type   | Modifiers
--------+---------+-----------
  x      | integer | not null
Primary key: a_pkey


and the latter:

           Table "a"
  Column |  Type   | Modifiers
--------+---------+-----------
  x      | integer | not null
Unique keys: a_pkey

See? it doesn't recognize x is primary key - just unique and not null (which is essentially the same thing)...

I hope, it helps...

Dima.

P.S. And for the 'unique' case, there is no difference at all, as far as I can see...