Skip to content

Commit e73131a

Browse files
adunstanadunstan
committed
DROP IF EXISTS for columns and constraints. Andres Freund.
1 parent 31d1f23 commit e73131a

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
static void ATExecDropColumn(List **wqueue, Relation rel, const char *colName,
287287
DropBehavior behavior,
288-
bool recurse, bool recursing);
288+
bool recurse, bool recursing,
289+
bool missing_ok);
289290
static void ATExecAddIndex(AlteredTableInfo *tab, Relation rel,
290291
IndexStmt *stmt, bool is_rebuild);
291292
static void ATExecAddConstraint(List **wqueue,
@@ -298,8 +299,9 @@ static void ATAddCheckConstraint(List **wqueue,
298299
static void ATAddForeignKeyConstraint(AlteredTableInfo *tab, Relation rel,
299300
FkConstraint *fkconstraint);
300301
static void ATExecDropConstraint(Relation rel, const char *constrName,
301-
DropBehavior behavior,
302-
bool recurse, bool recursing);
302+
DropBehavior behavior,
303+
bool recurse, bool recursing,
304+
bool missing_ok);
303305
static void ATPrepAlterColumnType(List **wqueue,
304306
AlteredTableInfo *tab, Relation rel,
305307
bool recurse, bool recursing,
@@ -2620,11 +2622,11 @@ ATExecCmd(List **wqueue, AlteredTableInfo *tab, Relation rel,
26202622
break;
26212623
case AT_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
case AT_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
case AT_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
case AT_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
case AT_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
case AT_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
static void
41614167
ATExecDropColumn(List **wqueue, Relation rel, const char *colName,
41624168
DropBehavior behavior,
4163-
bool recurse, bool recursing)
4169+
bool recurse, bool recursing,
4170+
bool missing_ok)
41644171
{
41654172
HeapTuple tuple;
41664173
Form_pg_attribute targetatt;
@@ -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
static void
53615379
ATExecDropConstraint(Relation rel, const char *constrName,
53625380
DropBehavior behavior,
5363-
bool recurse, bool recursing)
5381+
bool recurse, bool recursing,
5382+
bool missing_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
return newnode;
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
* AUTHOR DATE MAJOR 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+
| DROP opt_column IF_P EXISTS ColId opt_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
| DROP opt_column ColId opt_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+
| DROP CONSTRAINT IF_P EXISTS name opt_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
| DROP CONSTRAINT name opt_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
DropBehavior behavior; /* RESTRICT or CASCADE for DROP cases */
1148+
bool missing_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
alter table dropColumnchild drop column a;
817817
alter table only dropColumnChild drop column b;
818818

819+
820+
819821
-- these three should work
820822
alter table only dropColumn drop column e;
821823
alter table 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
alter table p2 drop column height;
915917

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

10611070
alter table anothertab alter column atcol1 type boolean
10621071
using case when atcol1 % 2 = 0 then true else false end;

0 commit comments

Comments
 (0)