Listing Databases in PostgreSQL Using psql

November 4, 2019

PostgreSQL server can contain multiple databases. Once you connect to server, PostgreSQL list database that has been created on the server. So we will discuss how to show databases in postgresql database server. You can show databases in PostgreSQL using psql command line. After login to psql you can check all the databases installed on the server. Using psql command prompt you can also switch databases in postgresql.

After installing PostgreSQL server user has to get familiar with the environment and need to check configuration related to the database. In PostgreSQL there are many third party tools available to do the task but psql is the inbuilt powerful command line tool like sqlplus in Oracle using which you can perform almost all the database related activities.

Must Read: How to Create user and assign privilege in PostgreSQL

PostgreSQL server contains many databases. Once you connect to server, PostgreSQL list database that has been created on the server.

PostgreSQL List Databases

You can create multiple databases inside PostgreSQL server. To view all the database created on the server you can use “\list” or “\l” command. You can also check all the postgres databases using the select statement mentioned below.

Show databases using psql select statement. 

postgres=# select datname from pg_database;

   datname
 postgres
  test11
  template1
  template0
 (4 rows)

You can also use \l or \l+ to show all databases in PostgreSQL server which you are connected to.

postgres=# \l
postgres=# \list

How to Switch databases in psql

Sometimes you need to switch between databases inside the psql in PostgreSQL. Switching between databases means you are exiting current database and connecting to other database inside psql. When you need to change between databases, you’ll use the \connect command, or \c followed by the database name as shown below:, 

 postgres=# \connect database_name
 postgres=# \c database_name

Check which PostgreSQL database you are connected to. 

SELECT current_database();

CONCLUSION: In this post we discussed about how to show all databases in postgresql server. Once you are connected to the database you can also switch database in postgresql.

Must Read: