From 2cd40adb85d1d5a7fd448721b326e73c1e318750 Mon Sep 17 00:00:00 2001 From: Andrew Dunstan Date: Wed, 29 Jul 2015 21:30:00 -0400 Subject: [PATCH] Add IF NOT EXISTS processing to ALTER TABLE ADD COLUMN MIME-Version: 1.0 Content-Type: text/plain; charset=utf8 Content-Transfer-Encoding: 8bit Fabrízio de Royes Mello, reviewed by Payal Singh, Alvaro Herrera and Michael Paquier. --- doc/src/sgml/ref/alter_table.sgml | 8 +- src/backend/commands/tablecmds.c | 52 +++++++++---- src/backend/parser/gram.y | 20 +++++ src/test/regress/expected/alter_table.out | 89 +++++++++++++++++++++++ src/test/regress/sql/alter_table.sql | 31 ++++++++ 5 files changed, 182 insertions(+), 18 deletions(-) diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgml index 207fec1758..1c1c18168e 100644 --- a/doc/src/sgml/ref/alter_table.sgml +++ b/doc/src/sgml/ref/alter_table.sgml @@ -36,7 +36,7 @@ ALTER TABLE ALL IN TABLESPACE name where action is one of: - ADD [ COLUMN ] column_name data_type [ COLLATE collation ] [ column_constraint [ ... ] ] + ADD [ COLUMN ] [ IF NOT EXISTS ] column_name data_type [ COLLATE collation ] [ column_constraint [ ... ] ] DROP [ COLUMN ] [ IF EXISTS ] column_name [ RESTRICT | CASCADE ] ALTER [ COLUMN ] column_name [ SET DATA ] TYPE data_type [ COLLATE collation ] [ USING expression ] ALTER [ COLUMN ] column_name SET DEFAULT expression @@ -96,11 +96,13 @@ ALTER TABLE ALL IN TABLESPACE name - ADD COLUMN + ADD COLUMN [ IF NOT EXISTS ] This form adds a new column to the table, using the same syntax as - . + . If IF NOT EXISTS + is specified and a column already exists with this name, + no error is thrown. diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c index b459b1eaec..970abd4b50 100644 --- a/src/backend/commands/tablecmds.c +++ b/src/backend/commands/tablecmds.c @@ -328,8 +328,9 @@ static void ATPrepAddColumn(List **wqueue, Relation rel, bool recurse, bool recu bool is_view, AlterTableCmd *cmd, LOCKMODE lockmode); static ObjectAddress ATExecAddColumn(List **wqueue, AlteredTableInfo *tab, Relation rel, ColumnDef *colDef, bool isOid, - bool recurse, bool recursing, LOCKMODE lockmode); -static void check_for_column_name_collision(Relation rel, const char *colname); + bool recurse, bool recursing, bool if_not_exists, LOCKMODE lockmode); +static bool check_for_column_name_collision(Relation rel, const char *colname, + bool if_not_exists); static void add_column_datatype_dependency(Oid relid, int32 attnum, Oid typid); static void add_column_collation_dependency(Oid relid, int32 attnum, Oid collid); static void ATPrepAddOids(List **wqueue, Relation rel, bool recurse, @@ -2304,7 +2305,7 @@ renameatt_internal(Oid myrelid, oldattname))); /* new name should not already exist */ - check_for_column_name_collision(targetrelation, newattname); + (void) check_for_column_name_collision(targetrelation, newattname, false); /* apply the update */ namestrcpy(&(attform->attname), newattname); @@ -3455,11 +3456,11 @@ ATExecCmd(List **wqueue, AlteredTableInfo *tab, Relation rel, case AT_AddColumnToView: /* add column via CREATE OR REPLACE * VIEW */ address = ATExecAddColumn(wqueue, tab, rel, (ColumnDef *) cmd->def, - false, false, false, lockmode); + false, false, false, false, lockmode); break; case AT_AddColumnRecurse: address = ATExecAddColumn(wqueue, tab, rel, (ColumnDef *) cmd->def, - false, true, false, lockmode); + false, true, false, cmd->missing_ok, lockmode); break; case AT_ColumnDefault: /* ALTER COLUMN DEFAULT */ address = ATExecColumnDefault(rel, cmd->name, cmd->def, lockmode); @@ -3572,14 +3573,14 @@ ATExecCmd(List **wqueue, AlteredTableInfo *tab, Relation rel, if (cmd->def != NULL) address = ATExecAddColumn(wqueue, tab, rel, (ColumnDef *) cmd->def, - true, false, false, lockmode); + true, false, false, cmd->missing_ok, lockmode); break; case AT_AddOidsRecurse: /* SET WITH OIDS */ /* Use the ADD COLUMN code, unless prep decided to do nothing */ if (cmd->def != NULL) address = ATExecAddColumn(wqueue, tab, rel, (ColumnDef *) cmd->def, - true, true, false, lockmode); + true, true, false, cmd->missing_ok, lockmode); break; case AT_DropOids: /* SET WITHOUT OIDS */ @@ -4677,7 +4678,7 @@ ATPrepAddColumn(List **wqueue, Relation rel, bool recurse, bool recursing, static ObjectAddress ATExecAddColumn(List **wqueue, AlteredTableInfo *tab, Relation rel, ColumnDef *colDef, bool isOid, - bool recurse, bool recursing, LOCKMODE lockmode) + bool recurse, bool recursing, bool if_not_exists, LOCKMODE lockmode) { Oid myrelid = RelationGetRelid(rel); Relation pgclass, @@ -4771,8 +4772,14 @@ ATExecAddColumn(List **wqueue, AlteredTableInfo *tab, Relation rel, elog(ERROR, "cache lookup failed for relation %u", myrelid); relkind = ((Form_pg_class) GETSTRUCT(reltup))->relkind; - /* new name should not already exist */ - check_for_column_name_collision(rel, colDef->colname); + /* skip if the name already exists and if_not_exists is true */ + if (!check_for_column_name_collision(rel, colDef->colname, if_not_exists)) + { + heap_close(attrdesc, RowExclusiveLock); + heap_freetuple(reltup); + heap_close(pgclass, RowExclusiveLock); + return InvalidObjectAddress; + } /* Determine the new attribute's number */ if (isOid) @@ -5002,7 +5009,8 @@ ATExecAddColumn(List **wqueue, AlteredTableInfo *tab, Relation rel, /* Recurse to child; return value is ignored */ ATExecAddColumn(wqueue, childtab, childrel, - colDef, isOid, recurse, true, lockmode); + colDef, isOid, recurse, true, + if_not_exists, lockmode); heap_close(childrel, NoLock); } @@ -5013,10 +5021,11 @@ ATExecAddColumn(List **wqueue, AlteredTableInfo *tab, Relation rel, /* * If a new or renamed column will collide with the name of an existing - * column, error out. + * column and if_not_exists is false then error out, else do nothing. */ -static void -check_for_column_name_collision(Relation rel, const char *colname) +static bool +check_for_column_name_collision(Relation rel, const char *colname, + bool if_not_exists) { HeapTuple attTuple; int attnum; @@ -5029,7 +5038,7 @@ check_for_column_name_collision(Relation rel, const char *colname) ObjectIdGetDatum(RelationGetRelid(rel)), PointerGetDatum(colname)); if (!HeapTupleIsValid(attTuple)) - return; + return true; attnum = ((Form_pg_attribute) GETSTRUCT(attTuple))->attnum; ReleaseSysCache(attTuple); @@ -5045,10 +5054,23 @@ check_for_column_name_collision(Relation rel, const char *colname) errmsg("column name \"%s\" conflicts with a system column name", colname))); else + { + if (if_not_exists) + { + ereport(NOTICE, + (errcode(ERRCODE_DUPLICATE_COLUMN), + errmsg("column \"%s\" of relation \"%s\" already exists, skipping", + colname, RelationGetRelationName(rel)))); + return false; + } + ereport(ERROR, (errcode(ERRCODE_DUPLICATE_COLUMN), errmsg("column \"%s\" of relation \"%s\" already exists", colname, RelationGetRelationName(rel)))); + } + + return true; } /* diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y index 8f053e47e8..6b02cecac1 100644 --- a/src/backend/parser/gram.y +++ b/src/backend/parser/gram.y @@ -1942,6 +1942,16 @@ alter_table_cmd: AlterTableCmd *n = makeNode(AlterTableCmd); n->subtype = AT_AddColumn; n->def = $2; + n->missing_ok = false; + $$ = (Node *)n; + } + /* ALTER TABLE ADD IF NOT EXISTS */ + | ADD_P IF_P NOT EXISTS columnDef + { + AlterTableCmd *n = makeNode(AlterTableCmd); + n->subtype = AT_AddColumn; + n->def = $5; + n->missing_ok = true; $$ = (Node *)n; } /* ALTER TABLE ADD COLUMN */ @@ -1950,6 +1960,16 @@ alter_table_cmd: AlterTableCmd *n = makeNode(AlterTableCmd); n->subtype = AT_AddColumn; n->def = $3; + n->missing_ok = false; + $$ = (Node *)n; + } + /* ALTER TABLE ADD COLUMN IF NOT EXISTS */ + | ADD_P COLUMN IF_P NOT EXISTS columnDef + { + AlterTableCmd *n = makeNode(AlterTableCmd); + n->subtype = AT_AddColumn; + n->def = $6; + n->missing_ok = true; $$ = (Node *)n; } /* ALTER TABLE ALTER [COLUMN] {SET DEFAULT |DROP DEFAULT} */ diff --git a/src/test/regress/expected/alter_table.out b/src/test/regress/expected/alter_table.out index 8d124961ce..028d6ed85c 100644 --- a/src/test/regress/expected/alter_table.out +++ b/src/test/regress/expected/alter_table.out @@ -2605,3 +2605,92 @@ ALTER TABLE logged1 SET UNLOGGED; -- silently do nothing DROP TABLE logged3; DROP TABLE logged2; DROP TABLE logged1; +-- test ADD COLUMN IF NOT EXISTS +CREATE TABLE test_add_column(c1 integer); +\d test_add_column +Table "public.test_add_column" + Column | Type | Modifiers +--------+---------+----------- + c1 | integer | + +ALTER TABLE test_add_column + ADD COLUMN c2 integer; +\d test_add_column +Table "public.test_add_column" + Column | Type | Modifiers +--------+---------+----------- + c1 | integer | + c2 | integer | + +ALTER TABLE test_add_column + ADD COLUMN c2 integer; -- fail because c2 already exists +ERROR: column "c2" of relation "test_add_column" already exists +\d test_add_column +Table "public.test_add_column" + Column | Type | Modifiers +--------+---------+----------- + c1 | integer | + c2 | integer | + +ALTER TABLE test_add_column + ADD COLUMN IF NOT EXISTS c2 integer; -- skipping because c2 already exists +NOTICE: column "c2" of relation "test_add_column" already exists, skipping +\d test_add_column +Table "public.test_add_column" + Column | Type | Modifiers +--------+---------+----------- + c1 | integer | + c2 | integer | + +ALTER TABLE test_add_column + ADD COLUMN c2 integer, -- fail because c2 already exists + ADD COLUMN c3 integer; +ERROR: column "c2" of relation "test_add_column" already exists +\d test_add_column +Table "public.test_add_column" + Column | Type | Modifiers +--------+---------+----------- + c1 | integer | + c2 | integer | + +ALTER TABLE test_add_column + ADD COLUMN IF NOT EXISTS c2 integer, -- skipping because c2 already exists + ADD COLUMN c3 integer; -- fail because c3 already exists +NOTICE: column "c2" of relation "test_add_column" already exists, skipping +\d test_add_column +Table "public.test_add_column" + Column | Type | Modifiers +--------+---------+----------- + c1 | integer | + c2 | integer | + c3 | integer | + +ALTER TABLE test_add_column + ADD COLUMN IF NOT EXISTS c2 integer, -- skipping because c2 already exists + ADD COLUMN IF NOT EXISTS c3 integer; -- skipping because c3 already exists +NOTICE: column "c2" of relation "test_add_column" already exists, skipping +NOTICE: column "c3" of relation "test_add_column" already exists, skipping +\d test_add_column +Table "public.test_add_column" + Column | Type | Modifiers +--------+---------+----------- + c1 | integer | + c2 | integer | + c3 | integer | + +ALTER TABLE test_add_column + ADD COLUMN IF NOT EXISTS c2 integer, -- skipping because c2 already exists + ADD COLUMN IF NOT EXISTS c3 integer, -- skipping because c3 already exists + ADD COLUMN c4 integer; +NOTICE: column "c2" of relation "test_add_column" already exists, skipping +NOTICE: column "c3" of relation "test_add_column" already exists, skipping +\d test_add_column +Table "public.test_add_column" + Column | Type | Modifiers +--------+---------+----------- + c1 | integer | + c2 | integer | + c3 | integer | + c4 | integer | + +DROP TABLE test_add_column; diff --git a/src/test/regress/sql/alter_table.sql b/src/test/regress/sql/alter_table.sql index 3feed7c232..aefb5c9376 100644 --- a/src/test/regress/sql/alter_table.sql +++ b/src/test/regress/sql/alter_table.sql @@ -1724,3 +1724,34 @@ ALTER TABLE logged1 SET UNLOGGED; -- silently do nothing DROP TABLE logged3; DROP TABLE logged2; DROP TABLE logged1; + +-- test ADD COLUMN IF NOT EXISTS +CREATE TABLE test_add_column(c1 integer); +\d test_add_column +ALTER TABLE test_add_column + ADD COLUMN c2 integer; +\d test_add_column +ALTER TABLE test_add_column + ADD COLUMN c2 integer; -- fail because c2 already exists +\d test_add_column +ALTER TABLE test_add_column + ADD COLUMN IF NOT EXISTS c2 integer; -- skipping because c2 already exists +\d test_add_column +ALTER TABLE test_add_column + ADD COLUMN c2 integer, -- fail because c2 already exists + ADD COLUMN c3 integer; +\d test_add_column +ALTER TABLE test_add_column + ADD COLUMN IF NOT EXISTS c2 integer, -- skipping because c2 already exists + ADD COLUMN c3 integer; -- fail because c3 already exists +\d test_add_column +ALTER TABLE test_add_column + ADD COLUMN IF NOT EXISTS c2 integer, -- skipping because c2 already exists + ADD COLUMN IF NOT EXISTS c3 integer; -- skipping because c3 already exists +\d test_add_column +ALTER TABLE test_add_column + ADD COLUMN IF NOT EXISTS c2 integer, -- skipping because c2 already exists + ADD COLUMN IF NOT EXISTS c3 integer, -- skipping because c3 already exists + ADD COLUMN c4 integer; +\d test_add_column +DROP TABLE test_add_column; -- 2.40.0