Re: bug on ALTER TABLE

["Joey K." <pguser@xxxxxxxxx>]

On Wed, May 14, 2008 at 4:35 PM, Martin Marques <martin@xxxxxxxxxxxxxxxxxxx> wrote:

Please, can someone explain how is it posible for ALTER TABLE to add a primary key column to a table without some intruction that would make it a real PK (NOT NULL and UNIQUE).

prueba=> CREATE TABLE nopk (
prueba(> textito varchar
prueba(> );
CREATE TABLE
prueba=> INSERT INTO nopk VALUES ('algo de texto');
INSERT 0 1
prueba=> INSERT INTO nopk VALUES ('otro texto');
INSERT 0 1
prueba=> ALTER TABLE nopk ADD COLUMN id INT PRIMARY KEY;
NOTICE:  ALTER TABLE / ADD PRIMARY KEY creará el índice implícito «nopk_pkey» para la tabla «nopk»
ALTER TABLE
prueba=> \d nopk
            Tabla «martin.nopk»
 Columna |       Tipo        | Modificadores
---------+-------------------+---------------
 textito | character varying |
 id      | integer           | not null
Índices:
   «nopk_pkey» PRIMARY KEY, btree (id)

prueba=> SELECT * FROM nopk WHERE id IS NULL;
   textito    | id
---------------+----
 algo de texto |
 otro texto    |
(2 filas)


So id is a pk with NULL values, which isn't right.

This bug recently fixed  in the 8.4 branch
http://archives.postgresql.org/message-id/37ed240d0804241207rb1c785crf63522791805461c@xxxxxxxxxxxxxx
http://wiki.postgresql.org/wiki/CommitFest:May

I'm not sure if this will be backported.

Joey