Take the 2-minute tour ×
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.

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.

share|improve this question
add comment

2 Answers

up vote 1 down vote accepted

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.

share|improve this answer
    
Too bad, ok so, is this possible with plpgsql? –  Caleb Feb 25 '11 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 Feb 25 '11 at 17:38
add comment

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 ...'
done
share|improve this answer
    
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 Feb 25 '11 at 18:47
    
That's a blah for windows not Python. –  Caleb Feb 25 '11 at 18:47
add comment

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.