List available databases in PostgreSQL

In this article, we will display how to list the available databases exist in PostgreSQL. Actually it can be done easily through some tools or utilities which is operated based on GUI (Graphical User Interface) such as PgAdmin as shown below :

But when using tools which is based on GUI (Graphical User Interface) is not possible, especially in an operating system runs in a CLI (Command Line Interface), we can just use several command to access PostgreSQL command console so that the information about available databases can be retrieved. Below is the steps on how to accomplish it :

1. Login to PostgreSQL comand console. It can be done just by typing the following command :

user@hostname:~# psql -Upostgres
Password for user postgres: 
psql (9.5.3, server 9.3.10)
Type "help" for help.

postgres=# 

2. After succesfully logged in, just type the following command in PostgreSQL command console as soon as successfully logged in  :

postgres=# \l

It is a command which is simply can be remembered by just associating it o be an abbreviation of list which is represented by the letter ‘l’.

postgres=# \l
List of databases
Name         |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
-------------+----------+----------+-------------+-------------+-----------------------
postgres     | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
template0    | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
|            |          |          |             | postgres=CTc/postgres
template1    | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
|            |          |          |             | postgres=CTc/postgres
test         | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
testing      | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
(5 rows)

List of databases available and it is displayed in the above output depends on the databases exists in the PostgreSQL database server and also the user whom used to log in to the database specified in the command described above in the first step. So, the list of available databases exist can be different if we use another user to be specified or to be used in the value of command parameter above for an example :

user@hostname:~# psql -Udbuser

Save