Re: bug on ALTER TABLE

> -----Original Message-----
> From: pgsql-general-owner(at)postgresql(dot)org [mailto:pgsql-general-
> owner(at)postgresql(dot)org] On Behalf Of Martin Marques
> Sent: Wednesday, May 14, 2008 4:35 PM
> To: pgsql-general(at)postgresql(dot)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.