In this tutorial, we will show you 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 provide this statement directly but offers you something similar. PostgreSQL provides you with two ways to show databases.
PostgreSQL listing databases using psql tool
If you are using psql tool to connect to PostgreSQL database server, you can issue the \l command to shows all databases in the current server as follows:
1 | \l |
For example, you can connect to the dvdrental sample database, and list all databases in that server as follows:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 | Server [localhost]: Database [postgres]: dvdrental Port [5432]: Username [postgres]: psql (9.4.2) dvdrental=# \l List of databases Name | Owner | Encoding | Collate | Ctype | Access privileges -----------+----------+----------+----------------------------+----------------------------+----------------------- dvdrental | postgres | UTF8 | English_United States.1252 | English_United States.1252 | postgres | postgres | UTF8 | English_United States.1252 | English_United States.1252 | template0 | postgres | UTF8 | English_United States.1252 | English_United States.1252 | =c/postgres + | | | | | postgres=CTc/postgres template1 | postgres | UTF8 | English_United States.1252 | English_United States.1252 | =c/postgres + | | | | | postgres=CTc/postgres (4 rows) dvdrental=# |
PostgreSQL listing databases using SELECT statement
Besides using the \l command, you can use the SELECT statement to query database names from the pg_database catalog that stores data about all available databases.
1 2 3 4 | SELECT datname FROM pg_database; |
It shows that we have four databases in the current database server.