0

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?

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
1
  • Thank you. If I have many tables, how to do it in batch? All the objects are in the main schema.
    – Miantian
    Jun 28 at 11:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.