PostgreSQL: How to add a column in every table of a database?

Question

I have a database with 169 tables

I need this column in every table:

wid integer not null primary key

I tried this(Thanks https://stackoverflow.com/users/27535/gbn for the solution):

SELECT 
  'ALTER TABLE ' + T.name + ' ADD foo int NULL'
FROM
  sys.tables AS T
WHERE
  T.is_ms_shipped = 0

But it didn't work on PostgreSQL.

It only worked on tsql.

How to add this column in every table at once ?

Answer 1

do $$
declare
    selectrow record;
begin
for selectrow in
    select 
      'ALTER TABLE '|| T.mytable || ' ADD COLUMN foo integer NULL' as script 
   from 
      ( 
        select tablename as mytable from  pg_tables where schemaname  ='public' --your schema name here
      ) t
loop
execute selectrow.script;
end loop;
end;
$$;

---

You can test whether all your tables altered with the new column using the following select

select 
     table_name,COLUMN_NAME
 from 
     INFORMATION_SCHEMA.COLUMNS 
 where 
   COLUMN_NAME='foo' -- column name here