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 ?
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
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.
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;
$$;
widin each and every tables in your database ???||not+