How to list all databases using PostgreSQL
A quick explanation of how to list all databases inside the `psql` tool in PostgreSQL, or using SQL
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
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+
):
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:
→ I wrote 17 books to help you become a better developer, download them all at $0 cost by joining my newsletter
→ JOIN MY CODING BOOTCAMP, an amazing cohort course that will be a huge step up in your coding career - covering React, Next.js - next edition February 2025