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!
