Search code examples
postgresqlalter-table

Alter all tables postgres set owner

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

Solution

  • 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.