Skip to content

How to list all databases using PostgreSQL

New Course Coming Soon:

Get Really Good at Git

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

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:

Are you intimidated by Git? Can’t figure out merge vs rebase? Are you afraid of screwing up something any time you have to do something in Git? Do you rely on ChatGPT or random people’s answer on StackOverflow to fix your problems? Your coworkers are tired of explaining Git to you all the time? Git is something we all need to use, but few of us really master it. I created this course to improve your Git (and GitHub) knowledge at a radical level. A course that helps you feel less frustrated with Git. Launching Summer 2024. Join the waiting list!
→ Read my SQL Tutorial on The Valley of Code

Here is how can I help you: