3

I'm new to PostgreSQL and I'm trying to change the table owner for a bunch of tables. I was able to change the ownership by logging in a the postgres user and executing alter table owner to user1, but when I login as user1 it still shows postgres as the owner. For instance, when logged in as the postgres user and executing \dt or select * from pg_tables where tableowner = 'user1' it shows user1 as the owner, but when I login as user1 and do the same thing it still shows postgres as the table owner. I've tried restarting the service and that didn't change anything. What am I missing?

  • 1
    Are both your postgres and user1 connected to the same database within the cluster? – Joishi Bodio Dec 22 '14 at 17:50
1

You must be seeing different tables from different database clusters, databases or schemas.

Either you are logging into a different database by mistake or you got yourself confused with tables of the same name in different schemas of the same database.

The latter would typically happen with two different roles that have different search_path settings or even the (default) setting:

search_path = "$user",public

Without schema-qualification, tables of the same name are found in the "current" schema first ...

Your test would also find the "wrong" table:

select * from pg_tables where tableowner = 'user1';

Test instead with:

select * from pg_tables where tablename = 'my_tbl';

You should see two or more rows for the same table name ...

Of just use schema-qualified table names to avoid possible confusion:

ALTER TABLE my_schema.my_tbl OWNER TO user1;
SELECT * FROM my_schema.my_tbl;

Start by reading about schemas in the manual.

|improve this answer|||||
  • For more information about the structural hierarchy of Cluster > Catalog > Schema > Table in Postgres, see my answer on StackOverflow. Includes a diagram that should help. – Basil Bourque Dec 29 '14 at 16:28
  • When trying your suggestion of querying based on tablename it still only shows one table. The only schema I have is the public schema. – bokoxev Jan 5 '15 at 17:53

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.