Waiting for 8.5 – pgsql: DROP IF EXISTS for columns and constraints.

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 Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.