Thread: No implicit index created when adding primary key with ALTER TABLE
Hi
I observed some strange behaviour when adding a primary key with ALTER TABLE:
Given CREATE TABLE mytable1 (id serial, name text);
I filled it with data then did a
CREATE TABLE mytable2 AS SELECT * FROM mytable1;
ALTER TABLE mytable2 ADD PRIMARY KEY(id);
The last command reports - as usual - that implicitly an index on id
("mytable_pkey") was created - but it did not! It adds only a primary
key constraint on id. Can anybody explain this?
Yours, S.
P.S. I have installed "PostgreSQL 9.1alpha1, compiled by Visual C++
build 1500, 32-bit".
On 14 June 2011 06:39, Stefan Keller <sfkeller@gmail.com> wrote:
> Hi
>
> I observed some strange behaviour when adding a primary key with ALTER TABLE:
>
> Given CREATE TABLE mytable1 (id serial, name text);
> I filled it with data then did a
> CREATE TABLE mytable2 AS SELECT * FROM mytable1;
> ALTER TABLE mytable2 ADD PRIMARY KEY(id);
>
> The last command reports - as usual - that implicitly an index on id
> ("mytable_pkey") was created - but it did not! It adds only a primary
> key constraint on id. Can anybody explain this?
>
> Yours, S.
>
> P.S. I have installed "PostgreSQL 9.1alpha1, compiled by Visual C++
> build 1500, 32-bit".
Shouldn't you be looking for mytable2_pkey?
--
Thom Brown
Twitter: @darkixion
IRC (freenode): dark_ixion
Registered Linux user: #516935
EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
Hi Thom 2011/6/14 Thom Brown <thom@linux.com>: > Shouldn't you be looking for mytable2_pkey? Yes; but that was my typo. I tried it several times on two tables. My explanation is that the message (saying that an index was implicitly created) is simply wrong. Yours, S.
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. 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.
regards, tom lane
On Tuesday, June 14, 2011 3:31:12 pm Stefan Keller wrote:
> Hi Thom
>
> 2011/6/14 Thom Brown <thom@linux.com>:
> > Shouldn't you be looking for mytable2_pkey?
>
> Yes; but that was my typo. I tried it several times on two tables.
> My explanation is that the message (saying that an index was
> implicitly created) is simply wrong.
Works here:
test(5432)aklaver=>SELECT version();
version
--------------------------------------------------------------------------------------------------
PostgreSQL 9.0.3 on i686-pc-linux-gnu, compiled by GCC gcc (Ubuntu
4.4.3-4ubuntu5) 4.4.3, 32-bit
(1 row)
test(5432)aklaver=> ALTER TABLE mytable2 ADD PRIMARY KEY(id);
NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index
"mytable2_pkey" for table "mytable2"
ALTER TABLE
test(5432)aklaver=>\d+ mytable2
Table "public.mytable2"
Column | Type | Modifiers | Storage | Description
--------+---------+-----------+----------+-------------
id | integer | not null | plain |
name | text | | extended |
Indexes:
"mytable2_pkey" PRIMARY KEY, btree (id)
Has OIDs: no
Note the btree designation.
>
> Yours, S.
--
Adrian Klaver
adrian.klaver@gmail.com
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
On Jun 16, 2011, at 6:52 AM, Stefan Keller wrote: > IMO this decision is actually questionable. Agreed. One should not have to look at constraints to figure out if there's an index. One should be able to check that directlyfrom the list of indexes. I would think this is really obvious. (But then again, db tools in general aren't really masters of the obvious when it comes to user interface...) -- Scott Ribe scott_ribe@elevated-dev.com http://www.elevated-dev.com/ (303) 722-0567 voice
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.klaver@gmail.com