I'm trying to get a list of databases from a Postgresql instance that contains a certain table. I've started using the system catalog tables to try and figure this out. I was trying to use pg_database, pg_class, pg_tables but I couldn't seem to find a way to link pg_class/pg_table back to pg_database to get the database for a given table.

If anyone has any ideas please let me know. It's probably just something stupid that I'm misising.

Thanks in advance.

2 Answers 2


This is not possible by a single query, because queries cannot span multiple databases, and the list of tables is only contained within the individual databases.

  • Too bad, ok so, is this possible with plpgsql?
    – Caleb
    Commented Feb 25, 2011 at 16:19
  • 3
    No, except when you use the dblink extension, which could connect to the other databases and query them. In this case one database should contain the dblink extensions, and the function to query the other databases, and then it should work.
    – Daniel
    Commented Feb 25, 2011 at 17:38

You need to query each database individually to check whether the table is there. Here is an example of a shell script:

for db in $(psql -At -c 'select datname from pg_database where not datistemplate'); do
    psql -d $db -c 'select * from pg_tables where ...'
  • Thanks. I'll give that a try although I'll probably change it to python since I'm stuck running this on a Windows box. Blah...
    – Caleb
    Commented Feb 25, 2011 at 18:47
  • That's a blah for windows not Python.
    – Caleb
    Commented Feb 25, 2011 at 18:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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