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

Commite73131a

Browse files
committed
DROP IF EXISTS for columns and constraints. Andres Freund.
1 parent31d1f23 commite73131a

File tree

8 files changed

+111
-32
lines changed

8 files changed

+111
-32
lines changed

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

Lines changed: 10 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
<!--
2-
$PostgreSQL: pgsql/doc/src/sgml/ref/alter_table.sgml,v 1.106 2009/05/03 20:45:43 tgl Exp $
2+
$PostgreSQL: pgsql/doc/src/sgml/ref/alter_table.sgml,v 1.107 2009/07/20 02:42:27 adunstan Exp $
33
PostgreSQL documentation
44
-->
55

@@ -33,15 +33,15 @@ ALTER TABLE <replaceable class="PARAMETER">name</replaceable>
3333
where <replaceable class="PARAMETER">action</replaceable> is one of:
3434

3535
ADD [ COLUMN ] <replaceable class="PARAMETER">column</replaceable> <replaceable class="PARAMETER">type</replaceable> [ <replaceable class="PARAMETER">column_constraint</replaceable> [ ... ] ]
36-
DROP [ COLUMN ] <replaceable class="PARAMETER">column</replaceable> [ RESTRICT | CASCADE ]
36+
DROP [ COLUMN ][ IF EXISTS ]<replaceable class="PARAMETER">column</replaceable> [ RESTRICT | CASCADE ]
3737
ALTER [ COLUMN ] <replaceable class="PARAMETER">column</replaceable> [ SET DATA ] TYPE <replaceable class="PARAMETER">type</replaceable> [ USING <replaceable class="PARAMETER">expression</replaceable> ]
3838
ALTER [ COLUMN ] <replaceable class="PARAMETER">column</replaceable> SET DEFAULT <replaceable class="PARAMETER">expression</replaceable>
3939
ALTER [ COLUMN ] <replaceable class="PARAMETER">column</replaceable> DROP DEFAULT
4040
ALTER [ COLUMN ] <replaceable class="PARAMETER">column</replaceable> { SET | DROP } NOT NULL
4141
ALTER [ COLUMN ] <replaceable class="PARAMETER">column</replaceable> SET STATISTICS <replaceable class="PARAMETER">integer</replaceable>
4242
ALTER [ COLUMN ] <replaceable class="PARAMETER">column</replaceable> SET STORAGE { PLAIN | EXTERNAL | EXTENDED | MAIN }
4343
ADD <replaceable class="PARAMETER">table_constraint</replaceable>
44-
DROP CONSTRAINT <replaceable class="PARAMETER">constraint_name</replaceable> [ RESTRICT | CASCADE ]
44+
DROP CONSTRAINT[ IF EXISTS ]<replaceable class="PARAMETER">constraint_name</replaceable> [ RESTRICT | CASCADE ]
4545
DISABLE TRIGGER [ <replaceable class="PARAMETER">trigger_name</replaceable> | ALL | USER ]
4646
ENABLE TRIGGER [ <replaceable class="PARAMETER">trigger_name</replaceable> | ALL | USER ]
4747
ENABLE REPLICA TRIGGER <replaceable class="PARAMETER">trigger_name</replaceable>
@@ -82,14 +82,17 @@ where <replaceable class="PARAMETER">action</replaceable> is one of:
8282
</varlistentry>
8383

8484
<varlistentry>
85-
<term><literal>DROP COLUMN</literal></term>
85+
<term><literal>DROP COLUMN [ IF EXISTS ]</literal></term>
8686
<listitem>
8787
<para>
8888
This form drops a column from a table. Indexes and
8989
table constraints involving the column will be automatically
9090
dropped as well. You will need to say <literal>CASCADE</> if
9191
anything outside the table depends on the column, for example,
9292
foreign key references or views.
93+
If <literal>IF EXISTS</literal> is specified and the column
94+
does not exist, no error is thrown. In this case a notice
95+
is issued instead.
9396
</para>
9497
</listitem>
9598
</varlistentry>
@@ -192,10 +195,12 @@ where <replaceable class="PARAMETER">action</replaceable> is one of:
192195
</varlistentry>
193196

194197
<varlistentry>
195-
<term><literal>DROP CONSTRAINT</literal></term>
198+
<term><literal>DROP CONSTRAINT [ IF EXISTS ]</literal></term>
196199
<listitem>
197200
<para>
198201
This form drops the specified constraint on a table.
202+
If <literal>IF EXISTS</literal> is specified and the constraint
203+
does not exist, no error is thrown. In this case a notice is issued instead.
199204
</para>
200205
</listitem>
201206
</varlistentry>

‎src/backend/commands/tablecmds.c‎

Lines changed: 53 additions & 23 deletions
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $PostgreSQL: pgsql/src/backend/commands/tablecmds.c,v 1.290 2009/07/16 06:33:42 petere Exp $
11+
* $PostgreSQL: pgsql/src/backend/commands/tablecmds.c,v 1.291 2009/07/20 02:42:27 adunstan Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -285,7 +285,8 @@ static void ATExecSetStorage(Relation rel, const char *colName,
285285
Node*newValue);
286286
staticvoidATExecDropColumn(List**wqueue,Relationrel,constchar*colName,
287287
DropBehaviorbehavior,
288-
boolrecurse,boolrecursing);
288+
boolrecurse,boolrecursing,
289+
boolmissing_ok);
289290
staticvoidATExecAddIndex(AlteredTableInfo*tab,Relationrel,
290291
IndexStmt*stmt,boolis_rebuild);
291292
staticvoidATExecAddConstraint(List**wqueue,
@@ -298,8 +299,9 @@ static void ATAddCheckConstraint(List **wqueue,
298299
staticvoidATAddForeignKeyConstraint(AlteredTableInfo*tab,Relationrel,
299300
FkConstraint*fkconstraint);
300301
staticvoidATExecDropConstraint(Relationrel,constchar*constrName,
301-
DropBehaviorbehavior,
302-
boolrecurse,boolrecursing);
302+
DropBehaviorbehavior,
303+
boolrecurse,boolrecursing,
304+
boolmissing_ok);
303305
staticvoidATPrepAlterColumnType(List**wqueue,
304306
AlteredTableInfo*tab,Relationrel,
305307
boolrecurse,boolrecursing,
@@ -2620,11 +2622,11 @@ ATExecCmd(List **wqueue, AlteredTableInfo *tab, Relation rel,
26202622
break;
26212623
caseAT_DropColumn:/* DROP COLUMN */
26222624
ATExecDropColumn(wqueue,rel,cmd->name,
2623-
cmd->behavior, false, false);
2625+
cmd->behavior, false, false,cmd->missing_ok);
26242626
break;
26252627
caseAT_DropColumnRecurse:/* DROP COLUMN with recursion */
26262628
ATExecDropColumn(wqueue,rel,cmd->name,
2627-
cmd->behavior, true, false);
2629+
cmd->behavior, true, false,cmd->missing_ok);
26282630
break;
26292631
caseAT_AddIndex:/* ADD INDEX */
26302632
ATExecAddIndex(tab,rel, (IndexStmt*)cmd->def, false);
@@ -2639,10 +2641,14 @@ ATExecCmd(List **wqueue, AlteredTableInfo *tab, Relation rel,
26392641
ATExecAddConstraint(wqueue,tab,rel,cmd->def, true);
26402642
break;
26412643
caseAT_DropConstraint:/* DROP CONSTRAINT */
2642-
ATExecDropConstraint(rel,cmd->name,cmd->behavior, false, false);
2644+
ATExecDropConstraint(rel,cmd->name,cmd->behavior,
2645+
false, false,
2646+
cmd->missing_ok);
26432647
break;
26442648
caseAT_DropConstraintRecurse:/* DROP CONSTRAINT with recursion */
2645-
ATExecDropConstraint(rel,cmd->name,cmd->behavior, true, false);
2649+
ATExecDropConstraint(rel,cmd->name,cmd->behavior,
2650+
true, false,
2651+
cmd->missing_ok);
26462652
break;
26472653
caseAT_AlterColumnType:/* ALTER COLUMN TYPE */
26482654
ATExecAlterColumnType(tab,rel,cmd->name, (TypeName*)cmd->def);
@@ -4160,7 +4166,8 @@ ATExecSetStorage(Relation rel, const char *colName, Node *newValue)
41604166
staticvoid
41614167
ATExecDropColumn(List**wqueue,Relationrel,constchar*colName,
41624168
DropBehaviorbehavior,
4163-
boolrecurse,boolrecursing)
4169+
boolrecurse,boolrecursing,
4170+
boolmissing_ok)
41644171
{
41654172
HeapTupletuple;
41664173
Form_pg_attributetargetatt;
@@ -4176,11 +4183,21 @@ ATExecDropColumn(List **wqueue, Relation rel, const char *colName,
41764183
* get the number of the attribute
41774184
*/
41784185
tuple=SearchSysCacheAttName(RelationGetRelid(rel),colName);
4179-
if (!HeapTupleIsValid(tuple))
4180-
ereport(ERROR,
4181-
(errcode(ERRCODE_UNDEFINED_COLUMN),
4182-
errmsg("column \"%s\" of relation \"%s\" does not exist",
4183-
colName,RelationGetRelationName(rel))));
4186+
if (!HeapTupleIsValid(tuple)){
4187+
if (!missing_ok){
4188+
ereport(ERROR,
4189+
(errcode(ERRCODE_UNDEFINED_COLUMN),
4190+
errmsg("column \"%s\" of relation \"%s\" does not exist",
4191+
colName,RelationGetRelationName(rel))));
4192+
}
4193+
else
4194+
{
4195+
ereport(NOTICE,
4196+
(errmsg("column \"%s\" of relation \"%s\" does not exist, skipping",
4197+
colName,RelationGetRelationName(rel))));
4198+
return;
4199+
}
4200+
}
41844201
targetatt= (Form_pg_attribute)GETSTRUCT(tuple);
41854202

41864203
attnum=targetatt->attnum;
@@ -4246,7 +4263,8 @@ ATExecDropColumn(List **wqueue, Relation rel, const char *colName,
42464263
{
42474264
/* Time to delete this child column, too */
42484265
ATExecDropColumn(wqueue,childrel,colName,
4249-
behavior, true, true);
4266+
behavior, true, true,
4267+
false);
42504268
}
42514269
else
42524270
{
@@ -5360,7 +5378,8 @@ createForeignKeyTriggers(Relation rel, FkConstraint *fkconstraint,
53605378
staticvoid
53615379
ATExecDropConstraint(Relationrel,constchar*constrName,
53625380
DropBehaviorbehavior,
5363-
boolrecurse,boolrecursing)
5381+
boolrecurse,boolrecursing,
5382+
boolmissing_ok)
53645383
{
53655384
List*children;
53665385
ListCell*child;
@@ -5422,12 +5441,22 @@ ATExecDropConstraint(Relation rel, const char *constrName,
54225441

54235442
systable_endscan(scan);
54245443

5425-
if (!found)
5426-
ereport(ERROR,
5427-
(errcode(ERRCODE_UNDEFINED_OBJECT),
5428-
errmsg("constraint \"%s\" of relation \"%s\" does not exist",
5429-
constrName,RelationGetRelationName(rel))));
5430-
5444+
if (!found){
5445+
if (!missing_ok){
5446+
ereport(ERROR,
5447+
(errcode(ERRCODE_UNDEFINED_OBJECT),
5448+
errmsg("constraint \"%s\" of relation \"%s\" does not exist",
5449+
constrName,RelationGetRelationName(rel))));
5450+
}
5451+
else
5452+
{
5453+
ereport(NOTICE,
5454+
(errmsg("constraint \"%s\" of relation \"%s\" does not exist, skipping",
5455+
constrName,RelationGetRelationName(rel))));
5456+
heap_close(conrel,RowExclusiveLock);
5457+
return;
5458+
}
5459+
}
54315460
/*
54325461
* Propagate to children as appropriate. Unlike most other ALTER
54335462
* routines, we have to do this one level of recursion at a time; we can't
@@ -5490,7 +5519,8 @@ ATExecDropConstraint(Relation rel, const char *constrName,
54905519
{
54915520
/* Time to delete this child constraint, too */
54925521
ATExecDropConstraint(childrel,constrName,behavior,
5493-
true, true);
5522+
true, true,
5523+
false);
54945524
}
54955525
else
54965526
{

‎src/backend/nodes/copyfuncs.c‎

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -15,7 +15,7 @@
1515
* Portions Copyright (c) 1994, Regents of the University of California
1616
*
1717
* IDENTIFICATION
18-
* $PostgreSQL: pgsql/src/backend/nodes/copyfuncs.c,v 1.433 2009/07/16 06:33:42 petere Exp $
18+
* $PostgreSQL: pgsql/src/backend/nodes/copyfuncs.c,v 1.434 2009/07/20 02:42:27 adunstan Exp $
1919
*
2020
*-------------------------------------------------------------------------
2121
*/
@@ -2272,6 +2272,7 @@ _copyAlterTableCmd(AlterTableCmd *from)
22722272
COPY_NODE_FIELD(def);
22732273
COPY_NODE_FIELD(transform);
22742274
COPY_SCALAR_FIELD(behavior);
2275+
COPY_SCALAR_FIELD(missing_ok);
22752276

22762277
returnnewnode;
22772278
}

‎src/backend/nodes/equalfuncs.c‎

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -22,7 +22,7 @@
2222
* Portions Copyright (c) 1994, Regents of the University of California
2323
*
2424
* IDENTIFICATION
25-
* $PostgreSQL: pgsql/src/backend/nodes/equalfuncs.c,v 1.356 2009/07/16 06:33:42 petere Exp $
25+
* $PostgreSQL: pgsql/src/backend/nodes/equalfuncs.c,v 1.357 2009/07/20 02:42:27 adunstan Exp $
2626
*
2727
*-------------------------------------------------------------------------
2828
*/
@@ -958,6 +958,7 @@ _equalAlterTableCmd(AlterTableCmd *a, AlterTableCmd *b)
958958
COMPARE_NODE_FIELD(def);
959959
COMPARE_NODE_FIELD(transform);
960960
COMPARE_SCALAR_FIELD(behavior);
961+
COMPARE_SCALAR_FIELD(missing_ok);
961962

962963
return true;
963964
}

‎src/backend/parser/gram.y‎

Lines changed: 23 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -11,7 +11,7 @@
1111
*
1212
*
1313
* IDENTIFICATION
14-
* $PostgreSQL: pgsql/src/backend/parser/gram.y,v 2.670 2009/07/16 06:33:43 petere Exp $
14+
* $PostgreSQL: pgsql/src/backend/parser/gram.y,v 2.671 2009/07/20 02:42:28 adunstan Exp $
1515
*
1616
* HISTORY
1717
* AUTHORDATEMAJOR EVENT
@@ -1610,13 +1610,24 @@ alter_table_cmd:
16101610
n->def = (Node *) makeString($6);
16111611
$$ = (Node *)n;
16121612
}
1613+
/* ALTER TABLE <name> DROP [COLUMN] IF EXISTS <colname> [RESTRICT|CASCADE]*/
1614+
|DROPopt_columnIF_PEXISTSColIdopt_drop_behavior
1615+
{
1616+
AlterTableCmd *n = makeNode(AlterTableCmd);
1617+
n->subtype = AT_DropColumn;
1618+
n->name =$5;
1619+
n->behavior =$6;
1620+
n->missing_ok =TRUE;
1621+
$$ = (Node *)n;
1622+
}
16131623
/* ALTER TABLE <name> DROP [COLUMN] <colname> [RESTRICT|CASCADE]*/
16141624
|DROPopt_columnColIdopt_drop_behavior
16151625
{
16161626
AlterTableCmd *n = makeNode(AlterTableCmd);
16171627
n->subtype = AT_DropColumn;
16181628
n->name =$3;
16191629
n->behavior =$4;
1630+
n->missing_ok =FALSE;
16201631
$$ = (Node *)n;
16211632
}
16221633
/*
@@ -1640,13 +1651,24 @@ alter_table_cmd:
16401651
n->def =$2;
16411652
$$ = (Node *)n;
16421653
}
1654+
/* ALTER TABLE <name> DROP CONSTRAINT IF EXISTS <name> [RESTRICT|CASCADE]*/
1655+
|DROPCONSTRAINTIF_PEXISTSnameopt_drop_behavior
1656+
{
1657+
AlterTableCmd *n = makeNode(AlterTableCmd);
1658+
n->subtype = AT_DropConstraint;
1659+
n->name =$5;
1660+
n->behavior =$6;
1661+
n->missing_ok =TRUE;
1662+
$$ = (Node *)n;
1663+
}
16431664
/* ALTER TABLE <name> DROP CONSTRAINT <name> [RESTRICT|CASCADE]*/
16441665
|DROPCONSTRAINTnameopt_drop_behavior
16451666
{
16461667
AlterTableCmd *n = makeNode(AlterTableCmd);
16471668
n->subtype = AT_DropConstraint;
16481669
n->name =$3;
16491670
n->behavior =$4;
1671+
n->missing_ok =FALSE;
16501672
$$ = (Node *)n;
16511673
}
16521674
/* ALTER TABLE <name> SET WITH OIDS*/

‎src/include/nodes/parsenodes.h‎

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -13,7 +13,7 @@
1313
* Portions Copyright (c) 1996-2009, PostgreSQL Global Development Group
1414
* Portions Copyright (c) 1994, Regents of the University of California
1515
*
16-
* $PostgreSQL: pgsql/src/include/nodes/parsenodes.h,v 1.396 2009/07/16 06:33:45 petere Exp $
16+
* $PostgreSQL: pgsql/src/include/nodes/parsenodes.h,v 1.397 2009/07/20 02:42:28 adunstan Exp $
1717
*
1818
*-------------------------------------------------------------------------
1919
*/
@@ -1145,6 +1145,7 @@ typedef struct AlterTableCmd/* one subcommand of an ALTER TABLE */
11451145
* index, constraint, or parent table */
11461146
Node*transform;/* transformation expr for ALTER TYPE */
11471147
DropBehaviorbehavior;/* RESTRICT or CASCADE for DROP cases */
1148+
boolmissing_ok;/* skip error if missing? */
11481149
}AlterTableCmd;
11491150

11501151

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

Lines changed: 10 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1150,6 +1150,12 @@ alter table gc1 drop column name;
11501150
ERROR: column "name" of relation "gc1" does not exist
11511151
-- should work and drop the attribute in all tables
11521152
alter table p2 drop column height;
1153+
-- IF EXISTS test
1154+
create table dropColumnExists ();
1155+
alter table dropColumnExists drop column non_existing; --fail
1156+
ERROR: column "non_existing" of relation "dropcolumnexists" does not exist
1157+
alter table dropColumnExists drop column if exists non_existing; --succeed
1158+
NOTICE: column "non_existing" of relation "dropcolumnexists" does not exist, skipping
11531159
select relname, attname, attinhcount, attislocal
11541160
from pg_class join pg_attribute on (pg_class.oid = pg_attribute.attrelid)
11551161
where relname in ('p1','p2','c1','gc1') and attnum > 0 and not attisdropped
@@ -1421,6 +1427,10 @@ alter table anothertab alter column atcol1 type boolean
14211427
ERROR: operator does not exist: boolean <= integer
14221428
HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.
14231429
alter table anothertab drop constraint anothertab_chk;
1430+
alter table anothertab drop constraint anothertab_chk; -- fails
1431+
ERROR: constraint "anothertab_chk" of relation "anothertab" does not exist
1432+
alter table anothertab drop constraint IF EXISTS anothertab_chk; -- succeeds
1433+
NOTICE: constraint "anothertab_chk" of relation "anothertab" does not exist, skipping
14241434
alter table anothertab alter column atcol1 type boolean
14251435
using case when atcol1 % 2 = 0 then true else false end;
14261436
select * from anothertab;

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

Lines changed: 9 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -816,6 +816,8 @@ create table dropColumnAnother (d int) inherits (dropColumnChild);
816816
altertable dropColumnchild drop column a;
817817
altertable only dropColumnChild drop column b;
818818

819+
820+
819821
-- these three should work
820822
altertable only dropColumn drop column e;
821823
altertable dropColumnChild drop column c;
@@ -913,6 +915,11 @@ alter table gc1 drop column name;
913915
-- should work and drop the attribute in all tables
914916
altertable p2 drop column height;
915917

918+
-- IF EXISTS test
919+
createtabledropColumnExists ();
920+
altertable dropColumnExists drop column non_existing;--fail
921+
altertable dropColumnExists drop column if exists non_existing;--succeed
922+
916923
select relname, attname, attinhcount, attislocal
917924
from pg_classjoin pg_attributeon (pg_class.oid=pg_attribute.attrelid)
918925
where relnamein ('p1','p2','c1','gc1')and attnum>0and not attisdropped
@@ -1057,6 +1064,8 @@ alter table anothertab alter column atcol1 drop default;
10571064
altertable anothertab alter column atcol1 typeboolean
10581065
using case when atcol1 %2=0 then true else false end;-- fails
10591066
altertable anothertab dropconstraint anothertab_chk;
1067+
altertable anothertab dropconstraint anothertab_chk;-- fails
1068+
altertable anothertab dropconstraint IF EXISTS anothertab_chk;-- succeeds
10601069

10611070
altertable anothertab alter column atcol1 typeboolean
10621071
using case when atcol1 %2=0 then true else false end;

0 commit comments

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