current community

your communities

more stack exchange communities

company blog
Stack Exchange Inbox Reputation and Badges
tour help
Sign up ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 3 down vote favorite

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.)

share|improve this question

1 Answer 1

up vote 4 down vote accepted

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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