Added A Column To Every Table In The Same Schema In PostgresSQL

Sometime, we may find out that we need to add a column to every table in the same schema or same database. For example, we want to add an audit column. The following query is for that.

do $$

declare

    addcolumn record;

    dm_schema VARCHAR(8) := ‘sales_dm';

begin

for addcolumn in

select

      'ALTER TABLE '|| dm_schema ||'.'|| T.targettable ||  ' ADD COLUMN  IF NOT EXISTS last_modified_timestamp timestamp  NULL' as sqlscript

   from

      (

        select tablename as targettable from  pg_tables where schemaname = dm_schema 

      ) t

loop

execute addcolumn.sqlscript;

end loop;

end;

$$;