How to get a list of databases owned by a user?

Question

Id like to discover (and delete) all databases owned by the owner in postgres 8.4.3. I am also new to postgres, and although I can and will read the entire manual today, I was forced to use

for i in $(psql -l |grep novicedba | awk '{print $1}') psql -d postgres -c " drop database \"$i\"" 

out of despair. What is the way postgresql do this?

Answer 1

Instead of grepping for novicedba, you can run the SQL query in the pg_database table:

 SELECT datname FROM pg_database JOIN pg_authid ON pg_database.datdba = pg_authid.oid WHERE rolname = 'novicedba' 

Once you do this, you will be able to iterate over the lines and discard them. This is safer than the first method, because it checks the exact field of the owner, and not the entire output string for the substring.