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

All we need is an easy explanation of the problem, so here it is.

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?

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

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

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply