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