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

2011-06-16 Thread Stefan Keller
Hi Tom 2011/6/15 Tom Lane t...@sss.pgh.pa.us: Stefan Keller sfkel...@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

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

2011-06-16 Thread Scott Ribe
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 directly from the list of indexes. I would think this is really obvious. (But

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

2011-06-16 Thread Adrian Klaver
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

[GENERAL] No implicit index created when adding primary key with ALTER TABLE

2011-06-14 Thread Stefan Keller
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

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

2011-06-14 Thread Thom Brown
On 14 June 2011 06:39, Stefan Keller sfkel...@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

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

2011-06-14 Thread Stefan Keller
Hi Thom 2011/6/14 Thom Brown t...@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. -- Sent via pgsql-general

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

2011-06-14 Thread Tom Lane
Stefan Keller sfkel...@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

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

2011-06-14 Thread Adrian Klaver
On Tuesday, June 14, 2011 3:31:12 pm Stefan Keller wrote: Hi Thom 2011/6/14 Thom Brown t...@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