Skip to content

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:

→ Download my free SQL Handbook!

THE VALLEY OF CODE

THE WEB DEVELOPER's MANUAL

You might be interested in those things I do:

  • Learn to code in THE VALLEY OF CODE, your your web development manual
  • Find a ton of Web Development projects to learn modern tech stacks in practice in THE VALLEY OF CODE PRO
  • I wrote 16 books for beginner software developers, DOWNLOAD THEM NOW
  • Every year I organize a hands-on cohort course coding BOOTCAMP to teach you how to build a complex, modern Web Application in practice (next edition February-March-April-May 2024)
  • Learn how to start a solopreneur business on the Internet with SOLO LAB (next edition in 2024)
  • Find me on X

Related posts that talk about database: