Re: default index created for primary key

On Wed, Dec 22, 2004 at 17:09:26 +0000,
vinita bansal <sagivini(at)hotmail(dot)com> wrote:
>
> I am actually migrating indexes from oracle database to postgres. I wanted
> to turn it off so that index on the same columns is not created again
> (index created for primary key of a table). I'll probably need to check in
> that case and not create the index if it is on the primary key of the table
> since that will be created by default.

You might be able to delete the indexes after the fact using data from
the catalog to find duplicates. This might be useful if there are so many
that looking for them by hand might be error prone.

> I am still not clear on why postgres has this restriction?
> By uniqueness, you mean to say that if later anyone wants to add a primary
> key constraint on a table which already has a primary key defined, postgres
> will use this index to determine that there is already a primary key
> defined and would not allow to add this constraint since a table cannot
> have two primary keys??

When you declare a primary key you are declaring a primary key constraint
at the same time. The way postgres implements that constraint is with
an index.