PostgreSQL: Modify OWNER on all tables simultaneously in PostgreSQL

How do I modify the owner of all tables in a PostgreSQL database?

I tried ALTER TABLE * OWNER TO new_owner but it doesn’t support the asterisk syntax.

This: http://archives.postgresql.org/pgsql-bugs/2007-10/msg00234.php is also a nice and fast solution, and works for multiple schemas in one database:

Tables

SELECT 'ALTER TABLE '|| schemaname || '."' || tablename ||'" OWNER TO my_new_owner;'
FROM pg_tables WHERE NOT schemaname IN ('pg_catalog', 'information_schema')
ORDER BY schemaname, tablename;

Sequences

SELECT 'ALTER SEQUENCE '|| sequence_schema || '."' || sequence_name ||'" OWNER TO my_new_owner;'
FROM information_schema.sequences WHERE NOT sequence_schema IN ('pg_catalog', 'information_schema')
ORDER BY sequence_schema, sequence_name;

Views

SELECT 'ALTER VIEW '|| table_schema || '."' || table_name ||'" OWNER TO my_new_owner;'
FROM information_schema.views WHERE NOT table_schema IN ('pg_catalog', 'information_schema')
ORDER BY table_schema, table_name;

Materialized Views

Based on this answer

SELECT 'ALTER TABLE '|| oid::regclass::text ||' OWNER TO my_new_owner;'
FROM pg_class WHERE relkind = 'm'
ORDER BY oid;

This generates all the required ALTER TABLE / ALTER SEQUENCE / ALTER VIEW statements, copy these and paste them back into plsql to run them.

Check your work in psql by doing:

\dt *.*
\ds *.*
\dv *.*