Last Updated: February 25, 2016
·
9.475K
· lbehnke

Change owner of multiple tables of a PostgreSQL database

I use the following tiny SQL script to change the owner of multiple tables at once. Here, the owner of all tables of the public schema is changed:

create or replace function exec(text) returns void as $$
begin
 execute $1;
end;
$$ language plpgsql;

select table_name || exec('alter table '||table_name||' owner to newuser') 
    as "Changed ownership" 
from information_schema.tables where table_schema='public';

This works also for other database structures such as sequences (PostgreSQL 8.3):

select sequence_name || exec('alter table '||sequence_name||' owner to newuser') 
    as "Changed ownership" 
from information_schema.sequences where sequence_schema='public';