PostgreSQL – Change owner of all tables within a schema

Check the current owner of the tables using the below query where schema_name is the name of the schema in lower case.

select tablename, tableowner from pg_tables where schemaname='schema_name';

Generate sql statements to change the owner of all the tables using below command where new_owner is the name of the new owner and schema_name is the name of the schema.

select 'Alter table '||t.schemaname||'.'||t.tablename ||' owner to new_owner;' from pg_tables t where schemaname='schema_name';                                              

Execute the output of the above sql command to change owner of all tables.

Verify the change by running the select command again.

select tablename, tableowner from pg_tables where schemaname='schema_name';
See also  PostgreSQL - Remove all special characters from a string