How To Alter The Ownership Of Some Tables Inside A Database From Postgres To Another User?

I have a database which contains significant number of tables. Some of the tables are owned by postgres user and not the one I created. I want to transfer the ownership of such tab

Solution 1:

Have you tried with an anonymous code block? This code block below selects all tables from the schema public that belongs to the user postgres and set the ownership to the user user:

DO $$
DECLARErow RECORD;
BEGINFORrowINSELECT*FROM pg_tables 
            WHERE schemaname ='public'AND tableowner ='postgres' LOOP 
  EXECUTE FORMAT('ALTER TABLE %I.%I OWNER TO user',row.schemaname,row.tablename);
 END LOOP;
END; 
$$;

Keep in mind that this operation will modify the ownership of all tables in your schema that belongs to the given user. Obviously you can further filter these tables by changing the pg_tables query in the loop. Take a look at:

SELECT*FROM pg_tables WHERE schemaname ='public'AND tableowner ='postgres';

Use it with care!

EDIT: To filter out a few tables from the selection above add a NOT IN, such as:

SELECT * FROM pg_tables 
WHEREschemaname='public'ANDtableowner='postgres'
AND tablename NOT IN('table1','table2','table3')