Bulk change table owner in postgres

 Published On September 29, 2015

How to modify the owner of many tables in a PostgreSQL database?

While there is a command REASSIGN OWNED to change the ownership of database objects owned by a database role, sometimes you need to change the owner of some tables only.

This is the script I run from pgAdmin.

---------------------------------------------------------------------------------
--- Bulk alter table owner for all tables in multiple schema
--- to be run into pgAdmin "Execute pgScript"
---------------------------------------------------------------------------------
--- hint: replace MY_USER with your actual user

SET @elements = select schemaname, tablename from pg_tables where schemaname in ('public','test','whatever');
SET @i = 0;
WHILE @i < LINES(@elements)
BEGIN
    SET @T = @elements[@i][0] + '.' + @elements[@i][1];
    ALTER TABLE @T OWNER TO MY_USER;
    SET @i = @i +1;
END

Tags: postgres dba sql

Comments:

comments powered by Disqus

© 2016 - Massimiliano Bernabé. All rights reserved
Built using Jekyll