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

Hi Tom

2011/6/15 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>:
> Stefan Keller <sfkeller(at)gmail(dot)com> writes:
>> My explanation is that the message (saying that an index was
>> implicitly created) is simply wrong.
>
> The correct explanation is that you're misinterpreting whatever output
> you're looking at.

Pls. don't treat inquirers like this - but thanks for the tip.
That's what I did:

CREATE TABLE mytable2(id int, name text);
ALTER TABLE mytable2 ADD PRIMARY KEY(id);

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.

> 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