PostgreSQL Show Databases

In this tutorial, you will learn how to show databases in a PostgreSQL database server.

In MySQL, you can show all databases in the database server using SHOW DATABASES statement.

PostgreSQL does not directly support the SHOW DATABASES statement but offers you something similar. PostgreSQL provides you with two ways to show databases in the current database server.

Listing databases in PostgreSQL using psql command

If you are using the psql tool to connect to PostgreSQL database server, you can issue the \l command to shows all databases in the current server as follows:

\lCode language: Shell Session (shell)

First, launch the psql tool. It will prompt you for the following information: server, database, port, and username. If you press the Enter keyboard, it will use the default value specified in the square brackets ([]). However, you need to provide the password for the user that you use to login:

Server [localhost]:
Database [postgres]:
Port [5432]:
Username [postgres]:
Password for user postgres:Code language: Shell Session (shell)

Second, use the \l command in the PostgreSQL prompt like this:

postgres=# \lCode language: Shell Session (shell)

It will show the following output:

If you want to get more information, you can use the \l+ command:

postgres=# \l+Code language: Shell Session (shell)

Listing databases in PostgreSQL using SELECT statement

Besides using the \l or \l+ command, you can use the SELECT statement to query database names from the pg_database catalog that contains information on databases.

SELECT datname FROM pg_database;Code language: SQL (Structured Query Language) (sql)

  datname
-----------
 postgres
 template1
 template0
 dvdrental
(4 rows)Code language: Shell Session (shell)

The query shows four databases in the current database server.

Summary

• Use \l or \l+ in psql to show all databases in the current PostgreSQL server.
• Use the SELECT statement to query data from the pg_database to get all databases.