On 205h of July Andrew Dunstan committed patch by Andres Freund :

Log Message:
-----------
DROP IF EXISTS for columns and constraints. Andres Freund.

The functionality is self-explanatory, so let's just show some usage examples:

Test table:

# create table test (a int4, b int4);
CREATE TABLE

Drop non-existing column old way:

# alter table test drop column c;
ERROR:  column "c" of relation "test" does not exist

and new way:

# alter table test drop column if exists c;
NOTICE:  column "c" of relation "test" does not exist, skipping
ALTER TABLE

Now, for constraints:

# alter table test add primary key (a);
NOTICE:  ALTER TABLE / ADD PRIMARY KEY will create implicit index "test_pkey" for table "test"
ALTER TABLE

New way of removal:

# alter table test drop constraint typo_pkey;
ERROR:  constraint "typo_pkey" of relation "test" does not exist

New way:

# alter table test drop constraint if exists typo_pkey;
NOTICE:  constraint "typo_pkey" of relation "test" does not exist, skipping
ALTER TABLE

With these additions now all objects can be dropped in “if exists" way (as far as I know).

Now, if we'd only get “CREATE UNLESS EXISTS" (and CREATE OR REPLACE for all object types) I would be soooo happy.

Leave a comment