Postgresql – Discovering databases with specific table on a PostgreSQL server

postgresql

Is there a way for a non-admin PostgreSQL user to discover which databases it can access on the server it is connected to, more specifically which of these databases have a table with a specific name that the user has enough permissions to see (and possibly query)?

As far as I can tell, the MySQL equivalent would be something like:

SELECT t.TABLE_SCHEMA FROM information_schema.`TABLES` AS t
    WHERE t.TABLE_NAME = 'the_table_name'

(This would be for PostgreSQL 8.4 preferably, but I'm also interested in solutions for 9.1 if this has changed.)

Best Answer

Not in a single query. The information_schema.tables view and the pg_catalog.pg_class table are per-database. You can't query tables across multiple databases in PostgreSQL without hacks like DBLink.

A PostgreSQL database is a bit more like an independent MySQL server, and a PostgreSQL schema is more like a MySQL database. If you want to be able to query across multiple namespaces in PostgreSQL, create them all as schemas within one database, instead of in multiple databases.

To list tables in all databases must connect to each database of interest. You can list them from select * from pg_catalog.pg_database where datistemplate = 'f';. The pg_catalog.pg_database table is global across all databases, so the same results are obtained by connecting to any database.

Related Question