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.
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):
|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.