How to list all databases using PostgreSQL

Jan 3, 2020

Learn how to list all databases in PostgreSQL, using the psql list meta-command or a SQL query that selects datname from the pg_database catalog table.

~~~

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):

$PostgreSQL terminal showing \list command output with databases in a table with Name, Owner, Encoding, Collate, Ctype, Access columns$

In this case, the databases list is

airbnbclone
nextbnb
postgres
test

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.

It’s a topic that does not matter now, but I just want you to know what templates are.

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

$PostgreSQL terminal showing \list+ command output with database details including Size and Tablespace columns$

The other way to list databases is by using a SQL query.

Run:

SELECT datname FROM pg_database
WHERE datistemplate = false;

This will list databases, excluding templates:

PostgreSQL terminal showing SQL query results listing database names: nextbnb, airbnbclone, test, postgres

Tagged: Database · All topics

~~~