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 ?

so you want to add column `wid` in each and every _tables_ in your database ??? — Vivek S., Sep 23 '14 at 11:11

Vivek S. · Accepted Answer · 2022-03-07T13:56:00.150

16

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

edited Mar 07 '22 at 13:56

answered Sep 23 '14 at 11:19

Vivek S.

19,945
7
68
85

Its absolutely wrong Winged. Your logic is incorrect in this case. – Sep 23 '14 at 11:25
@ajay state your reason – Vivek S. Sep 23 '14 at 11:28
As mentioned you have used wrong looping condition. – Sep 23 '14 at 11:30
@a_horse_with_no_name still i dont think it will work in this case. – Sep 23 '14 at 11:32
@a_horse_with_no_name i've tested this in my db all tables altered with new column `foo` – Vivek S. Sep 23 '14 at 11:34
@a_horse_with_no_name if possible test this in your environment – Vivek S. Sep 23 '14 at 11:35
1

@GabrielLidenor see my latest EDIT – Vivek S. Sep 23 '14 at 11:45
Sorry, the missing indention kind of tricked me. The execute is indeed inside the loop. You should be using `pg_tables` instead of `pg_stat_user_tables` in the first script though. – Sep 23 '14 at 11:48
@Insider sounds like an issue related to your environment. Try with limit `where COLUMN_NAME='foo' limit 2` – Vivek S. Sep 15 '22 at 06:25

hbn · Answer 2 · 2014-09-23T11:39:20.800

Try this (change 'public' to whatever schema you're doing this in)

DO $$
DECLARE 
    row record; 
    cmd text;
BEGIN
    FOR row IN SELECT schemaname, tablename FROM pg_tables WHERE schemaname = 'public' LOOP
        cmd := format('ALTER TABLE %I.%I ADD COLUMN foo SERIAL PRIMARY KEY ', row.schemaname, row.tablename);
        RAISE NOTICE '%', cmd;
        -- EXECUTE cmd;
    END LOOP;
END
$$ LANGUAGE plpgsql;

If you run as is, it'll show you the commands. Uncomment the EXECUTE line to actually perform the alterations.

I'd run within a transaction so you can roll back if you're not happy with the results.

Note that the type is SERIAL - the column type will be integer, but also creates a sequence owned by the table and defaults the column value to the next value of that sequence.

Sagar Pise · Answer 3 · 2021-01-14T06:48:38.733

We may need to check column is already exist or not.

Tested on PostgreSQL V10

do $$
declare selectrow record;
begin
for selectrow in
    select 'ALTER TABLE '|| T.mytable || ' ADD COLUMN x_is_exported boolean DEFAULT FALSE' as script 
   from (select tablename as mytable from  pg_tables where schemaname  ='public') t
loop
    begin
        execute selectrow.script;
        EXCEPTION WHEN duplicate_column THEN CONTINUE;
    END;
end loop;
end;
$$;

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

3 Answers3