Re: No implicit index created when adding primary key with ALTER TABLE

On Thursday, June 16, 2011 5:52:43 am Stefan Keller wrote:
> Hi Tom
>

>
> Then I used pgAdminIII to look for the pkey index and there was
> nothing. That was and still is actually the problem.
>
> When I subsequently created an index
> CREATE INDEX ON mytable2(id);
> ...or two (:->)
> CREATE INDEX ON mytable2(id);
>
> Postgres silently created additional indexes and pgAdminIII obviously
> showed these (which is all right) - but still without showing the
> initial pkey index - which to me is misleading.

Well your initial post was about the index not being created, not about it not
being displayed in pgAdmin:) This seems to be a design decision on the part of
pgAdmin. As you mention below it does show up in psql. You might want to ping
the pgAdmin folks with a feature request.

>
> > Every unique or pkey constraint has an underlying
> > index --- the index is the implementation mechanism for the constraint,
> > so this is assuredly so. Some tools that show both constraints and
> > indexes will omit constraint-associated indexes from the listing, since
> > otherwise they'd be showing duplicate information.
>
> IMO this decision is actually questionable. It makes no sense to me to
> suppress the indication if indexes: Either there is one or not,
> disregarding of constraints. In psql the commands \d+ and \di report
> indexes too.
>
> Yours, Stefan

--
Adrian Klaver
adrian(dot)klaver(at)gmail(dot)com