Re: bug on ALTER TABLE

> -----Original Message-----
> From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-
> owner@postgresql.org] On Behalf Of Martin Marques
> Sent: Wednesday, May 14, 2008 4:35 PM
> To: pgsql-general@postgresql.org
> Subject: [GENERAL] bug on ALTER TABLE
>
> 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.

That's what you asked for.

osastest=# CREATE TABLE nopk (
osastest(#  textito varchar
osastest(# );
CREATE TABLE
osastest=# INSERT INTO nopk VALUES ('algo de texto');
INSERT 0 1
osastest=# INSERT INTO nopk VALUES ('otro texto');
INSERT 0 1
osastest=# ALTER TABLE nopk ADD COLUMN id INT PRIMARY KEY;
NOTICE:  ALTER TABLE / ADD PRIMARY KEY will create implicit index "nopk_pkey" for table "nopk"
ALTER TABLE

Same as you up to here.  Get rid of the bad column:

osastest=# ALTER TABLE nopk drop COLUMN id;
ALTER TABLE

Now, make a primary key that is not null:
osastest=# ALTER TABLE nopk ADD COLUMN id INT NOT NULL PRIMARY KEY;
NOTICE:  ALTER TABLE / ADD PRIMARY KEY will create implicit index "nopk_pkey" for table "nopk"
ALTER TABLE

Now, let's make a smarter one:
osastest=# ALTER TABLE nopk drop COLUMN id;
ALTER TABLE
osastest=# ALTER TABLE nopk ADD COLUMN id SERIAL NOT NULL PRIMARY KEY;
NOTICE:  ALTER TABLE will create implicit sequence "nopk_id_seq" for serial column "nopk.id"
NOTICE:  ALTER TABLE / ADD PRIMARY KEY will create implicit index "nopk_pkey" for table "nopk"
ALTER TABLE
osastest=#

Now we have a primary key that can auto-increment itself.  I would use bigint instead of int, personally.