PostgreSQL Tutorial: Show Databases

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

Table of Contents

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:

\l

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:

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

postgres=# \l

It will show the following output:

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

postgres=# \l+

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;

  datname
-----------
 postgres
 template1
 template0
 dvdrental
(4 rows)

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.

See more

PostgreSQL Administration