how to get list of databases that a user owns?

Question

Id like to find out ( and delete ) all the databases owned by an owner in postgres 8.4.3 I'm new to postgres also, and although I can , and will , read the whole 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 desperation. What's the postgresql way to do this?

Answer 1

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

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

Once you have that, you can then iterate over the rows and drop each one. This is safer than the first method, because it's checking the exact owner field, and not the whole line of output for a substring.