Wednesday, April 01, 2020

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;
$$;

No comments: