Listing Databases in Postgres

When working with an SQL database, especially in a development environment, it is easy to lose track of which databases are in which instance.

MySQL has a really convenient way of listing its databases with the command

SHOW DATABASES;

in a client session or even programmatically. But then, structurally MySQL is a good deal simpler - or at least it was - than Postgres.

To get a similar result in Postgres can be done by querying the system tables:

SELECT datname FROM pg_database;

This is good to know as it can be used with other tables to get more sophisticated answers. For instance,

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

selects only databases belonging to novicedba. And of course these statements can be executed programmatically too.

There are times when this level of sophistication is not required; perhaps we just want to see the proper name of a database so we can dump it. A simpler list can be rendered with

\l

if you are in a Postgres session, or

psql -l

from the command-line.