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:

CREATE OR REPLACE FUNCTION add_version_column_to_all_tables()
RETURNS VOID
AS $$
DECLARE
 my_row RECORD;
BEGIN
 FOR my_row IN 
 SELECT table_name
 FROM information_schema.tables
 WHERE table_schema = 'public'
 LOOP
 IF NOT EXISTS
 (
 SELECT attname FROM pg_attribute WHERE attrelid = 
 (SELECT oid FROM pg_class WHERE relname = my_row.table_name )
 AND attname = 'version'
 )
 THEN
 EXECUTE('ALTER TABLE ' || my_row.table_name || ' ADD COLUMN version int NOT NULL DEFAULT 0;');
 END IF;
 END LOOP;
END
$$
LANGUAGE plpgsql;

SELECT add_version_column_to_all_tables();

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