How to change table and sequence owner to another user with PostgreSQL?

Question

Created these tables and sequences under postgres user:

table creation schema

SET search_path TO main;
CREATE TABLE table1 ...
CREATE TABLE table2 ...
CREATE TABLE table3 ...

sequence creation schema

CREATE SEQUENCE main.seq1...
CREATE SEQUENCE main.seq2...
CREATE SEQUENCE main.seq3...

Now want to change all of them to an another owner named user1. How to do without recreate these objects as they are been using?

Laurenz Albe · Accepted Answer · 2021-06-28T12:08:08.653

ALTER TABLE/SEQUENCE objectname OWNER TO somerole;

You can only execute that as a superuser or as a role what is member of both the old and new owning role.

If you want to do that for many tables at the same time, use format to generate a script:

SELECT format(
          'ALTER TABLE %I OWNER TO somerole;',
          oid::regclass
       )
FROM pg_class WHERE relname LIKE 'table%';

Then execute that script. If you are using psql, you can use \gexec to do that in one statement:

SELECT format(
          'ALTER TABLE %I OWNER TO somerole;',
          oid::regclass
       )
FROM pg_class WHERE relname LIKE 'table%' \gexec

1 Answers1