bug on ALTER TABLE

On Wed, May 14, 2008 at 4:35 PM, Martin Marques <martin@marquesminen.com.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

-----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.