Alter all tables postgres set owner-CodePudding

I need a postgresql statement to run through the public schema (all tables) and update the owner. I know the manual command is ALTER TABLE public.<table_name> OWNER TO <username>;

However I'm not sure how to put this into a script/format which will loop through each table and update the owner?

This is for a postgres 9.6 database (it will be run on a test db).

Thanks

CodePudding user response：

Connect to the database with psql and run

SELECT format(
          'ALTER TABLE public.%I OWNER TO user_name',
          table_name
       )
FROM information_schema.tables
WHERE table_schema = 'public'
  AND table_type = 'BASE TABLE' \gexec

\gexec will execute each line of the query result as a statement.

Do upgrade.