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