Postgresql – Command to add column to all tables in a schema

dynamic-sqlpostgresqlpostgresql-9.4

Could you please give the command to add a column to all the tables if the table doesn't contain that column in a schema?

e.g. The schema schema1 contains 50 tables in which 20 tables contain a column named colTest.

I need to add the column colTest to the remaining 30 tables.

Best Answer

You can do this with a loop and dynamic SQL:

do
$$
declare
  tname record;
begin
  for tname in select t.table_schema, t.table_name
               from information_schema.tables t
               where table_schema = 'schema1' --<< change schema name here
                 and not exists (select * 
                                 from information_schema.columns c
                                 where (c.table_schema, c.table_name) = (t.table_schema, t.table_name)
                                   and c.column_name = 'coltest') 
  loop
    -- change column definition in the following string
    execute format('alter table %I.%I add column coltest integer', tname.table_schema, tname.table_name);
  end loop;
end;
$$
Related Question