PostgreSQL-15, how to retrieve table owner by given database name and table name?

Question

I have multiple PostgreSQL-15 databases with different names to fulfil our business operational data, such as: pilot, production and archive data in different purpose.

I am trying to write a shell script to check every table's owner under different database with their own table name. For example:

select tableowner from pg_catalog.pg_tables where schemaname = 'public' AND tablename = '$1' AND pg_database.datname = '$2';

my postgresql response as:

ERROR:  missing FROM-clause entry for table "pg_database"

I tried to retrieve pg_catalog.pg_tables properties and found there is no database information was kept in the property.

My question is: How I can query table properties by give table name and database name in single psql statement in shell script, some thing like:

tableowner=`psql -h $prim_host -p $port -U postgres -t -c "select tableowner from pg_catalog.pg_tables where schemaname = 'public' AND tablename = '$1' AND pg_database.datname = '$2';"`

Please advise!

Answer 1

You have to connect to the database that contains the table:

tableowner=$(psql -Atq -h $host -p $port -U postgres -d "$2" -c "SELECT relowner::regrole FROM pg_class WHERE relname = '$1'")