August 7, 2023
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
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 : Username [postgres]: Password for user postgres:
Second, use the
\l command in the PostgreSQL prompt like this:
It will show the following output:
If you want to get more information, you can use the
Listing databases in PostgreSQL using SELECT statement
Besides using the
\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.
psqlto show all databases in the current PostgreSQL server.
- Use the
SELECTstatement to query data from the
pg_databaseto get all databases.