How to list all databases using PostgreSQL

You can perform this task in 2 ways.

One is using psql.

Type the command \list (or \l), and PostgreSQL will show you the list of databases (and templates):

In this case, the databases list is

• airbnbclone
• nextbnb
• postgres
• test

postgres is the maintenance database the installer creates, so tools and administrators always have somewhere to connect.

template0 and template1 are templates.

Templates are templates for new databases, and you can use them to pre-populate new databases using the syntax CREATE DATABASE databasename TEMPLATE template0.

By default, the template used when creating a new database using CREATE DATABASE databasename is template1.

This default has a practical consequence: anything you create inside template1 shows up in every database you create afterwards. If new databases contain tables you never asked for, someone connected to template1 by mistake and left objects there. template0 stays clean, which is why restores use it.

A more advanced view, which includes the disk size of each single database, can be retrieved using \list+ (or \l+):

The size column is the fastest way to find the database that grew when disk space runs low.

Listing databases with SQL

\l is a psql meta-command, so it only works inside psql. From application code, an ORM, or any other client, query the catalog instead.

Run:

SELECT datname FROM pg_database
WHERE datistemplate = false;

This will list databases, excluding templates:

Drop the datistemplate = false filter when you want templates included.

Listing databases doubles as a verification step: after CREATE DATABASE or a restore, the new name should appear here. If it does not, check which server you are connected to with \conninfo, because the usual explanation is a connection to a different cluster than you thought.