Re: bug on ALTER TABLE

On Wed, May 14, 2008 at 4:35 PM, Martin Marques <martin(at)marquesminen(dot)com(dot)ar>
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@mail.gmail.com
http://wiki.postgresql.org/wiki/CommitFest:May

I'm not sure if this will be backported.

Joey