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.


You can do this with a loop and dynamic SQL:

  tname record;
  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') 
    -- 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;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.