Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit2cd40ad

Browse files
committed
Add IF NOT EXISTS processing to ALTER TABLE ADD COLUMN
Fabrízio de Royes Mello, reviewed by Payal Singh, Alvaro Herrera andMichael Paquier.
1 parent632cd9f commit2cd40ad

File tree

5 files changed

+182
-18
lines changed

5 files changed

+182
-18
lines changed

‎doc/src/sgml/ref/alter_table.sgml‎

Lines changed: 5 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -36,7 +36,7 @@ ALTER TABLE ALL IN TABLESPACE <replaceable class="PARAMETER">name</replaceable>
3636

3737
<phrase>where <replaceable class="PARAMETER">action</replaceable> is one of:</phrase>
3838

39-
ADD [ COLUMN ] <replaceable class="PARAMETER">column_name</replaceable> <replaceable class="PARAMETER">data_type</replaceable> [ COLLATE <replaceable class="PARAMETER">collation</replaceable> ] [ <replaceable class="PARAMETER">column_constraint</replaceable> [ ... ] ]
39+
ADD [ COLUMN ][ IF NOT EXISTS ]<replaceable class="PARAMETER">column_name</replaceable> <replaceable class="PARAMETER">data_type</replaceable> [ COLLATE <replaceable class="PARAMETER">collation</replaceable> ] [ <replaceable class="PARAMETER">column_constraint</replaceable> [ ... ] ]
4040
DROP [ COLUMN ] [ IF EXISTS ] <replaceable class="PARAMETER">column_name</replaceable> [ RESTRICT | CASCADE ]
4141
ALTER [ COLUMN ] <replaceable class="PARAMETER">column_name</replaceable> [ SET DATA ] TYPE <replaceable class="PARAMETER">data_type</replaceable> [ COLLATE <replaceable class="PARAMETER">collation</replaceable> ] [ USING <replaceable class="PARAMETER">expression</replaceable> ]
4242
ALTER [ COLUMN ] <replaceable class="PARAMETER">column_name</replaceable> SET DEFAULT <replaceable class="PARAMETER">expression</replaceable>
@@ -96,11 +96,13 @@ ALTER TABLE ALL IN TABLESPACE <replaceable class="PARAMETER">name</replaceable>
9696

9797
<variablelist>
9898
<varlistentry>
99-
<term><literal>ADD COLUMN</literal></term>
99+
<term><literal>ADD COLUMN [ IF NOT EXISTS ]</literal></term>
100100
<listitem>
101101
<para>
102102
This form adds a new column to the table, using the same syntax as
103-
<xref linkend="SQL-CREATETABLE">.
103+
<xref linkend="SQL-CREATETABLE">. If <literal>IF NOT EXISTS</literal>
104+
is specified and a column already exists with this name,
105+
no error is thrown.
104106
</para>
105107
</listitem>
106108
</varlistentry>

‎src/backend/commands/tablecmds.c‎

Lines changed: 37 additions & 15 deletions
Original file line numberDiff line numberDiff line change
@@ -328,8 +328,9 @@ static void ATPrepAddColumn(List **wqueue, Relation rel, bool recurse, bool recu
328328
boolis_view,AlterTableCmd*cmd,LOCKMODElockmode);
329329
staticObjectAddressATExecAddColumn(List**wqueue,AlteredTableInfo*tab,
330330
Relationrel,ColumnDef*colDef,boolisOid,
331-
boolrecurse,boolrecursing,LOCKMODElockmode);
332-
staticvoidcheck_for_column_name_collision(Relationrel,constchar*colname);
331+
boolrecurse,boolrecursing,boolif_not_exists,LOCKMODElockmode);
332+
staticboolcheck_for_column_name_collision(Relationrel,constchar*colname,
333+
boolif_not_exists);
333334
staticvoidadd_column_datatype_dependency(Oidrelid,int32attnum,Oidtypid);
334335
staticvoidadd_column_collation_dependency(Oidrelid,int32attnum,Oidcollid);
335336
staticvoidATPrepAddOids(List**wqueue,Relationrel,boolrecurse,
@@ -2304,7 +2305,7 @@ renameatt_internal(Oid myrelid,
23042305
oldattname)));
23052306

23062307
/* new name should not already exist */
2307-
check_for_column_name_collision(targetrelation,newattname);
2308+
(void)check_for_column_name_collision(targetrelation,newattname, false);
23082309

23092310
/* apply the update */
23102311
namestrcpy(&(attform->attname),newattname);
@@ -3455,11 +3456,11 @@ ATExecCmd(List **wqueue, AlteredTableInfo *tab, Relation rel,
34553456
caseAT_AddColumnToView:/* add column via CREATE OR REPLACE
34563457
* VIEW */
34573458
address=ATExecAddColumn(wqueue,tab,rel, (ColumnDef*)cmd->def,
3458-
false, false, false,lockmode);
3459+
false, false, false,false,lockmode);
34593460
break;
34603461
caseAT_AddColumnRecurse:
34613462
address=ATExecAddColumn(wqueue,tab,rel, (ColumnDef*)cmd->def,
3462-
false, true, false,lockmode);
3463+
false, true, false,cmd->missing_ok,lockmode);
34633464
break;
34643465
caseAT_ColumnDefault:/* ALTER COLUMN DEFAULT */
34653466
address=ATExecColumnDefault(rel,cmd->name,cmd->def,lockmode);
@@ -3572,14 +3573,14 @@ ATExecCmd(List **wqueue, AlteredTableInfo *tab, Relation rel,
35723573
if (cmd->def!=NULL)
35733574
address=
35743575
ATExecAddColumn(wqueue,tab,rel, (ColumnDef*)cmd->def,
3575-
true, false, false,lockmode);
3576+
true, false, false,cmd->missing_ok,lockmode);
35763577
break;
35773578
caseAT_AddOidsRecurse:/* SET WITH OIDS */
35783579
/* Use the ADD COLUMN code, unless prep decided to do nothing */
35793580
if (cmd->def!=NULL)
35803581
address=
35813582
ATExecAddColumn(wqueue,tab,rel, (ColumnDef*)cmd->def,
3582-
true, true, false,lockmode);
3583+
true, true, false,cmd->missing_ok,lockmode);
35833584
break;
35843585
caseAT_DropOids:/* SET WITHOUT OIDS */
35853586

@@ -4677,7 +4678,7 @@ ATPrepAddColumn(List **wqueue, Relation rel, bool recurse, bool recursing,
46774678
staticObjectAddress
46784679
ATExecAddColumn(List**wqueue,AlteredTableInfo*tab,Relationrel,
46794680
ColumnDef*colDef,boolisOid,
4680-
boolrecurse,boolrecursing,LOCKMODElockmode)
4681+
boolrecurse,boolrecursing,boolif_not_exists,LOCKMODElockmode)
46814682
{
46824683
Oidmyrelid=RelationGetRelid(rel);
46834684
Relationpgclass,
@@ -4771,8 +4772,14 @@ ATExecAddColumn(List **wqueue, AlteredTableInfo *tab, Relation rel,
47714772
elog(ERROR,"cache lookup failed for relation %u",myrelid);
47724773
relkind= ((Form_pg_class)GETSTRUCT(reltup))->relkind;
47734774

4774-
/* new name should not already exist */
4775-
check_for_column_name_collision(rel,colDef->colname);
4775+
/* skip if the name already exists and if_not_exists is true */
4776+
if (!check_for_column_name_collision(rel,colDef->colname,if_not_exists))
4777+
{
4778+
heap_close(attrdesc,RowExclusiveLock);
4779+
heap_freetuple(reltup);
4780+
heap_close(pgclass,RowExclusiveLock);
4781+
returnInvalidObjectAddress;
4782+
}
47764783

47774784
/* Determine the new attribute's number */
47784785
if (isOid)
@@ -5002,7 +5009,8 @@ ATExecAddColumn(List **wqueue, AlteredTableInfo *tab, Relation rel,
50025009

50035010
/* Recurse to child; return value is ignored */
50045011
ATExecAddColumn(wqueue,childtab,childrel,
5005-
colDef,isOid,recurse, true,lockmode);
5012+
colDef,isOid,recurse, true,
5013+
if_not_exists,lockmode);
50065014

50075015
heap_close(childrel,NoLock);
50085016
}
@@ -5013,10 +5021,11 @@ ATExecAddColumn(List **wqueue, AlteredTableInfo *tab, Relation rel,
50135021

50145022
/*
50155023
* If a new or renamed column will collide with the name of an existing
5016-
* column,error out.
5024+
* column and if_not_exists is false thenerror out, else do nothing.
50175025
*/
5018-
staticvoid
5019-
check_for_column_name_collision(Relationrel,constchar*colname)
5026+
staticbool
5027+
check_for_column_name_collision(Relationrel,constchar*colname,
5028+
boolif_not_exists)
50205029
{
50215030
HeapTupleattTuple;
50225031
intattnum;
@@ -5029,7 +5038,7 @@ check_for_column_name_collision(Relation rel, const char *colname)
50295038
ObjectIdGetDatum(RelationGetRelid(rel)),
50305039
PointerGetDatum(colname));
50315040
if (!HeapTupleIsValid(attTuple))
5032-
return;
5041+
return true;
50335042

50345043
attnum= ((Form_pg_attribute)GETSTRUCT(attTuple))->attnum;
50355044
ReleaseSysCache(attTuple);
@@ -5045,10 +5054,23 @@ check_for_column_name_collision(Relation rel, const char *colname)
50455054
errmsg("column name \"%s\" conflicts with a system column name",
50465055
colname)));
50475056
else
5057+
{
5058+
if (if_not_exists)
5059+
{
5060+
ereport(NOTICE,
5061+
(errcode(ERRCODE_DUPLICATE_COLUMN),
5062+
errmsg("column \"%s\" of relation \"%s\" already exists, skipping",
5063+
colname,RelationGetRelationName(rel))));
5064+
return false;
5065+
}
5066+
50485067
ereport(ERROR,
50495068
(errcode(ERRCODE_DUPLICATE_COLUMN),
50505069
errmsg("column \"%s\" of relation \"%s\" already exists",
50515070
colname,RelationGetRelationName(rel))));
5071+
}
5072+
5073+
return true;
50525074
}
50535075

50545076
/*

‎src/backend/parser/gram.y‎

Lines changed: 20 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1942,6 +1942,16 @@ alter_table_cmd:
19421942
AlterTableCmd *n = makeNode(AlterTableCmd);
19431943
n->subtype = AT_AddColumn;
19441944
n->def =$2;
1945+
n->missing_ok =false;
1946+
$$ = (Node *)n;
1947+
}
1948+
/* ALTER TABLE <name> ADD IF NOT EXISTS <coldef>*/
1949+
|ADD_PIF_PNOTEXISTScolumnDef
1950+
{
1951+
AlterTableCmd *n = makeNode(AlterTableCmd);
1952+
n->subtype = AT_AddColumn;
1953+
n->def =$5;
1954+
n->missing_ok =true;
19451955
$$ = (Node *)n;
19461956
}
19471957
/* ALTER TABLE <name> ADD COLUMN <coldef>*/
@@ -1950,6 +1960,16 @@ alter_table_cmd:
19501960
AlterTableCmd *n = makeNode(AlterTableCmd);
19511961
n->subtype = AT_AddColumn;
19521962
n->def =$3;
1963+
n->missing_ok =false;
1964+
$$ = (Node *)n;
1965+
}
1966+
/* ALTER TABLE <name> ADD COLUMN IF NOT EXISTS <coldef>*/
1967+
|ADD_PCOLUMNIF_PNOTEXISTScolumnDef
1968+
{
1969+
AlterTableCmd *n = makeNode(AlterTableCmd);
1970+
n->subtype = AT_AddColumn;
1971+
n->def =$6;
1972+
n->missing_ok =true;
19531973
$$ = (Node *)n;
19541974
}
19551975
/* ALTER TABLE <name> ALTER [COLUMN] <colname> {SET DEFAULT <expr>|DROP DEFAULT}*/

‎src/test/regress/expected/alter_table.out‎

Lines changed: 89 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2605,3 +2605,92 @@ ALTER TABLE logged1 SET UNLOGGED; -- silently do nothing
26052605
DROP TABLE logged3;
26062606
DROP TABLE logged2;
26072607
DROP TABLE logged1;
2608+
-- test ADD COLUMN IF NOT EXISTS
2609+
CREATE TABLE test_add_column(c1 integer);
2610+
\d test_add_column
2611+
Table "public.test_add_column"
2612+
Column | Type | Modifiers
2613+
--------+---------+-----------
2614+
c1 | integer |
2615+
2616+
ALTER TABLE test_add_column
2617+
ADD COLUMN c2 integer;
2618+
\d test_add_column
2619+
Table "public.test_add_column"
2620+
Column | Type | Modifiers
2621+
--------+---------+-----------
2622+
c1 | integer |
2623+
c2 | integer |
2624+
2625+
ALTER TABLE test_add_column
2626+
ADD COLUMN c2 integer; -- fail because c2 already exists
2627+
ERROR: column "c2" of relation "test_add_column" already exists
2628+
\d test_add_column
2629+
Table "public.test_add_column"
2630+
Column | Type | Modifiers
2631+
--------+---------+-----------
2632+
c1 | integer |
2633+
c2 | integer |
2634+
2635+
ALTER TABLE test_add_column
2636+
ADD COLUMN IF NOT EXISTS c2 integer; -- skipping because c2 already exists
2637+
NOTICE: column "c2" of relation "test_add_column" already exists, skipping
2638+
\d test_add_column
2639+
Table "public.test_add_column"
2640+
Column | Type | Modifiers
2641+
--------+---------+-----------
2642+
c1 | integer |
2643+
c2 | integer |
2644+
2645+
ALTER TABLE test_add_column
2646+
ADD COLUMN c2 integer, -- fail because c2 already exists
2647+
ADD COLUMN c3 integer;
2648+
ERROR: column "c2" of relation "test_add_column" already exists
2649+
\d test_add_column
2650+
Table "public.test_add_column"
2651+
Column | Type | Modifiers
2652+
--------+---------+-----------
2653+
c1 | integer |
2654+
c2 | integer |
2655+
2656+
ALTER TABLE test_add_column
2657+
ADD COLUMN IF NOT EXISTS c2 integer, -- skipping because c2 already exists
2658+
ADD COLUMN c3 integer; -- fail because c3 already exists
2659+
NOTICE: column "c2" of relation "test_add_column" already exists, skipping
2660+
\d test_add_column
2661+
Table "public.test_add_column"
2662+
Column | Type | Modifiers
2663+
--------+---------+-----------
2664+
c1 | integer |
2665+
c2 | integer |
2666+
c3 | integer |
2667+
2668+
ALTER TABLE test_add_column
2669+
ADD COLUMN IF NOT EXISTS c2 integer, -- skipping because c2 already exists
2670+
ADD COLUMN IF NOT EXISTS c3 integer; -- skipping because c3 already exists
2671+
NOTICE: column "c2" of relation "test_add_column" already exists, skipping
2672+
NOTICE: column "c3" of relation "test_add_column" already exists, skipping
2673+
\d test_add_column
2674+
Table "public.test_add_column"
2675+
Column | Type | Modifiers
2676+
--------+---------+-----------
2677+
c1 | integer |
2678+
c2 | integer |
2679+
c3 | integer |
2680+
2681+
ALTER TABLE test_add_column
2682+
ADD COLUMN IF NOT EXISTS c2 integer, -- skipping because c2 already exists
2683+
ADD COLUMN IF NOT EXISTS c3 integer, -- skipping because c3 already exists
2684+
ADD COLUMN c4 integer;
2685+
NOTICE: column "c2" of relation "test_add_column" already exists, skipping
2686+
NOTICE: column "c3" of relation "test_add_column" already exists, skipping
2687+
\d test_add_column
2688+
Table "public.test_add_column"
2689+
Column | Type | Modifiers
2690+
--------+---------+-----------
2691+
c1 | integer |
2692+
c2 | integer |
2693+
c3 | integer |
2694+
c4 | integer |
2695+
2696+
DROP TABLE test_add_column;

‎src/test/regress/sql/alter_table.sql‎

Lines changed: 31 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1724,3 +1724,34 @@ ALTER TABLE logged1 SET UNLOGGED; -- silently do nothing
17241724
DROPTABLE logged3;
17251725
DROPTABLE logged2;
17261726
DROPTABLE logged1;
1727+
1728+
-- test ADD COLUMN IF NOT EXISTS
1729+
CREATETABLEtest_add_column(c1integer);
1730+
\d test_add_column
1731+
ALTERTABLE test_add_column
1732+
ADD COLUMN c2integer;
1733+
\d test_add_column
1734+
ALTERTABLE test_add_column
1735+
ADD COLUMN c2integer;-- fail because c2 already exists
1736+
\d test_add_column
1737+
ALTERTABLE test_add_column
1738+
ADD COLUMN IF NOT EXISTS c2integer;-- skipping because c2 already exists
1739+
\d test_add_column
1740+
ALTERTABLE test_add_column
1741+
ADD COLUMN c2integer,-- fail because c2 already exists
1742+
ADD COLUMN c3integer;
1743+
\d test_add_column
1744+
ALTERTABLE test_add_column
1745+
ADD COLUMN IF NOT EXISTS c2integer,-- skipping because c2 already exists
1746+
ADD COLUMN c3integer;-- fail because c3 already exists
1747+
\d test_add_column
1748+
ALTERTABLE test_add_column
1749+
ADD COLUMN IF NOT EXISTS c2integer,-- skipping because c2 already exists
1750+
ADD COLUMN IF NOT EXISTS c3integer;-- skipping because c3 already exists
1751+
\d test_add_column
1752+
ALTERTABLE test_add_column
1753+
ADD COLUMN IF NOT EXISTS c2integer,-- skipping because c2 already exists
1754+
ADD COLUMN IF NOT EXISTS c3integer,-- skipping because c3 already exists
1755+
ADD COLUMN c4integer;
1756+
\d test_add_column
1757+
DROPTABLE test_add_column;

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp