February 1st, 2012 by depesz | Tags: , , , , , | 4 comments »
Did it help? If yes - maybe you can help me?

On 23th of January, Simon Riggs committed patch:

ALTER <thing> [IF EXISTS] ... allows silent DDL if required,
e.g. ALTER FOREIGN TABLE IF EXISTS foo RENAME TO bar
 
Pavel Stehule

This adds important capability – change object of it exists, and not raise exception if it doesn't.

Conditional DDL was always big point on a list of missing features – I wrote about general solution back in 2008.

Recently we had patches that added some conditional ddl.

But still not everything is possible.

Patch by Pavel adds another step towards ultimate goal – being able to specify any DDL in a way that will not raise exception – thus allowing single-transaction schema changes.

To show simplistic example of what's possible thanks to this patch:

$ alter table if exists zzz add column q int4;
NOTICE:  relation "zzz" does not exist, skipping
ALTER TABLE

Previously, if I'd ran it without zzz table, I'd get:

$ alter table if exists zzz add column q int4;
ERROR:  syntax error at or near "exists"
LINE 1: alter table if exists zzz add column q int4;
                       ^
 
$ alter table zzz add column q int4;
ERROR:  relation "zzz" does not exist

This is all great, and while (as far as I know) this patch was written to help with some pg_dump functionalities, it will clearly be beneficial for others, like me, who tend to use some kind of versioning system for their databases.

Out of curiosity, I checked how many operations (create/alter/drop) on common objects (index/table/sequence/view/function) are yet to be implemented. Results (state as of Wed, 01 Feb 2012 14:05:18 +0100):

object create alter drop
index not supported works works
table works works works
sequence not supported works works
view not supported works works
function not supported not supported works

In case of views and function – we can get good results with “drop if exists" and then unconditional create, but still having “if exists" on all create/alter would be good for simplicity sake.

  1. 4 comments

  2. # georgm
    Feb 1, 2012

    what about “create or replace function” – this seems a bit like a combination of create and alter…

  3. Feb 1, 2012

    @georgm:
    yes, but it cannot do some things – like change return datatype.

  4. # Mat
    Aug 17, 2015

    Error on Sequences while using “IF EXISTS” in 9.1.

    ERROR: syntax error at or near “EXISTS”
    LINE 1 : ALTER SEQUENCE IF EXISTS

    Was added at same time (9.2) :
    http://www.postgresql.org/docs/9.2/static/sql-altersequence.html

  5. Aug 17, 2015

    @Mat:

    yes, and ?

    As title says – it’s “Waiting for 9.2” – as in: this is new change in PostgreSQL 9.2. I never said that it’s in 9.1. I never even suggested it.

    So what exactly you’re pointing here?

Leave a comment