From dbbceabb5673c7e4c6b785750562920a06283416 Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Thu, 24 Apr 2008 20:18:15 +0000 Subject: [PATCH] Fix ALTER TABLE ADD COLUMN ... PRIMARY KEY so that the new column is correctly checked to see if it's been initialized to all non-nulls. The implicit NOT NULL constraint was not being checked during the ALTER (in fact, not even if there was an explicit NOT NULL too), because ATExecAddColumn neglected to set the flag needed to make the test happen. This has been broken since the capability was first added, in 8.0. Brendan Jurd, per a report from Kaloyan Iliev. --- src/backend/commands/tablecmds.c | 5 +++++ src/test/regress/expected/alter_table.out | 12 ++++++++++++ src/test/regress/sql/alter_table.sql | 10 ++++++++++ 3 files changed, 27 insertions(+) diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c index 9f4481104b..ee195c6d04 100644 --- a/src/backend/commands/tablecmds.c +++ b/src/backend/commands/tablecmds.c @@ -3138,6 +3138,11 @@ ATExecAddColumn(AlteredTableInfo *tab, Relation rel, tab->newvals = lappend(tab->newvals, newval); } + /* + * If the new column is NOT NULL, tell Phase 3 it needs to test that. + */ + tab->new_notnull |= colDef->is_not_null; + /* * Add needed dependency entries for the new column. */ diff --git a/src/test/regress/expected/alter_table.out b/src/test/regress/expected/alter_table.out index b6e46393b4..b408205aca 100644 --- a/src/test/regress/expected/alter_table.out +++ b/src/test/regress/expected/alter_table.out @@ -433,6 +433,18 @@ create table atacc1 ( test int ); alter table atacc1 add constraint atacc_test1 primary key (test1); ERROR: column "test1" named in key does not exist drop table atacc1; +-- adding a new column as primary key to a non-empty table. +-- should fail unless the column has a non-null default value. +create table atacc1 ( test int ); +insert into atacc1 (test) values (0); +-- add a primary key column without a default (fails). +alter table atacc1 add column test2 int primary key; +NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index "atacc1_pkey" for table "atacc1" +ERROR: column "test2" contains null values +-- now add a primary key column with a default (succeeds). +alter table atacc1 add column test2 int default 0 primary key; +NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index "atacc1_pkey" for table "atacc1" +drop table atacc1; -- something a little more complicated create table atacc1 ( test int, test2 int); -- add a primary key constraint diff --git a/src/test/regress/sql/alter_table.sql b/src/test/regress/sql/alter_table.sql index 8690f61dbe..cc899a8bef 100644 --- a/src/test/regress/sql/alter_table.sql +++ b/src/test/regress/sql/alter_table.sql @@ -453,6 +453,16 @@ create table atacc1 ( test int ); alter table atacc1 add constraint atacc_test1 primary key (test1); drop table atacc1; +-- adding a new column as primary key to a non-empty table. +-- should fail unless the column has a non-null default value. +create table atacc1 ( test int ); +insert into atacc1 (test) values (0); +-- add a primary key column without a default (fails). +alter table atacc1 add column test2 int primary key; +-- now add a primary key column with a default (succeeds). +alter table atacc1 add column test2 int default 0 primary key; +drop table atacc1; + -- something a little more complicated create table atacc1 ( test int, test2 int); -- add a primary key constraint -- 2.39.5