PostgreSQL - Add a Column to All Tables in the Database Unless it Exists

To be able to use NHibernate – and as a general purpose and learning exercise – I wanted to write a script that adds a version column of type integer to all tables in the database, unless such column exists (in Rails the schema_migrations table has a version column for example).

In SQL Server we have the “secret” sproc sp_MSforeachtable for this purpose, but how to bend the stricter syntax of plpgslq to achieve something similar? After a number of hours of trial and error, here's is a script that actually appears to work, at least with PostgreSQL 9.0 and probably earlier versions:

Im sure this script can be optimized, please post any improvements in the columns.

Hope it helps.

dirk: @Sheroz: You have to preplace all inverted commas ‘ (these are created by Wordpress from single quotes) with normal single quotes. Please let me know if you still have problems.

Shehroz: This script gave following error. ERROR: mismatched parentheses at or near ";" LINE 20: ...able_name || ‘ ADD COLUMN version int NOT NULL DEFAULT 0;’); Could you please help in tracing it?

Rez: I had error executing this. Problem was in ALTER TABLE statement. It requires name of the table to be in quotes. I changed EXECUTE... line into this EXECUTE('ALTER TABLE "' || result_row.table_name ||'" ADD COLUMN customer_id int;'); and it worked just fine. Thanks again.

ravi: worked. Thanks

Bill: Rez, I had the same issue. It was because the quotes copied from this page were (left quotes) and (right quotes). When I replaced them with (single quotes) it worked fine! WHERE table_schema = 'public' EXECUTE('ALTER TABLE ' || my_row.table_name || ' ADD COLUMN version int NOT NULL DEFAULT 0;');

Posted: 12 June 2011