How can I filter databases by owner using psql?

Question

I know I can list my postgres databases with the following command:

# list only the databases
psql -U openerp -q -t -c "select datname from pg_database;" template1

Or

# show a full listing of databases
psql template1 -U openerp -l

How can I filter the databases by owner 'openerp'? datdba field is just a number... how can I map it?

Answer 1

For things like this, you'll want to query pg_database directly. The owner field is a reference to pg_catalog.pg_authid.oid, but this is a superuser-only accessible table. There's a view, pg_catalog.pg_roles that exposes the id as the oid attribute, so you can:

select 
 datname,
 rolname AS dbowner
from pg_database d 
 inner join pg_roles r on (d.datdba = r.oid);

• Luis Masuelli: U're right. After digging for hours I found this approach (yes, i'm a pg n00b)
• Ramy Moussa: It works! I just added a where clause to filter by my desired rolname. where rolname='ramy' .