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

Hi Tom

2011/6/15 Tom Lane <tgl@sss.pgh.pa.us>:
> Stefan Keller <sfkeller@gmail.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