How to List All Databases in PostgreSQL

In this quick tutorial, we will quickly learn how to list all the databases in a PostgreSQL Server using simple commands.
Captain Salem 4 min read
How to List All Databases in PostgreSQL


PostgreSQL is a powerful, open-source relational database management system (RDBMS) that provides robust data storage, processing, and retrieval capabilities. It is designed to handle high volumes of data, emphasizing reliability, data integrity, and data security.

PostgreSQL operates as a client-server model where the client communicates with the PostgreSQL server to manage data. The PostgreSQL server processes incoming queries, executes them, and returns the result to the client. It uses a multi-version concurrency control (MVCC) system to manage multiple transactions simultaneously on the same data. Each transaction sees a snapshot of the data, and all transactions run concurrently without locking each other. MVCC provides excellent concurrency while maintaining data consistency, making PostgreSQL a superb choice for high-transaction environments.

PostgreSQL has robust indexing options that allow for efficient data retrieval. Indexes are data structures that help the database engine locate data quickly, essential for high-performance applications. PostgreSQL supports several indexes, including B-tree, hash, GiST, SP-GiST, GIN, and BRIN.


  1. PostgreSQL Server
  2. Permissions to View and List databases

Method 1 - PostgreSQL List Databases Using the PSQL Tool

psql is a command-line tool for managing and interacting with PostgreSQL databases. It allows users to execute SQL commands and queries, manage database objects such as tables, indexes, and views, and perform administrative tasks such as user management, backups, and restores.

psql provides robust features, including tab completion, syntax highlighting, and history management. It also supports customizing the output format of queries and importing and exporting data in various formats, such as CSV and JSON.

On Windows, launch the psql tool by searching SQL Shell from the start menu.

This should open the SQL Shell allowing you to connect to the PostgreSQL Server. You can press ENTER to accept the default values as shown in the prompt below:

Server [localhost]:
Database [postgres]:
Port [5432]:
Username [postgres]:
Password for user postgres:
psql (15.2)
WARNING: Console code page (437) differs from Windows code page (1252)
         8-bit characters might not work correctly. See psql reference
         page "Notes for Windows users" for details.
Type "help" for help.

Note: Ensure to provide the password for the postgres user as defined during the installation.

Once logged in, you should find yourself in your PostgreSQL Shell ready to execute commands to the server.

To list of all databases currently on the server, including the database name, the owner, encoding, collation, ctype, and access privileges, run the command:


The \l command tells psql to show all the databases on the server and the corresponding information. An example output is as shown:

   Name    |  Owner   | Encoding |          Collate           |           Ctype            | ICU Locale | Locale Provider |   Access privileges
 postgres  | postgres | UTF8     | English_United States.1252 | English_United States.1252 |            | libc            |
 template0 | postgres | UTF8     | English_United States.1252 | English_United States.1252 |            | libc            | =c/postgres          +
           |          |          |                            |                            |            |                 | postgres=CTc/postgres
 template1 | postgres | UTF8     | English_United States.1252 | English_United States.1252 |            | libc            | =c/postgres          +
           |          |          |                            |                            |            |                 | postgres=CTc/postgres
(3 rows)

The above output shows all the available database names, owner, encoding, collation, and privileges.

We can also show additional information about a given database using the \l+ command.


Method 2 - PostgreSQL List Databases Using PLSQL Query

We can also use a SQL Query to list all the databases in a PostgreSQL Server as shown in the command below:

postgres=# select datname, encoding from pg_database;

The command should list all the database names and their respective encoding as shown in the example output below:

  datname  | encoding
 postgres  |        6
 template1 |        6
 template0 |        6
(3 rows)

Method 3 - PostgreSQL List Databases Using GUI

You can also use the graphical interface for PostgreSQL to show all the databases in the server.

How to install PostgreSQL and pgAdmin 4 on Windows
In this article, you will learn how to install and configure PostgreSQL Server and pgAdmin4 on Windows.

Start by logging into the server from pgAdmin.

Once logged in, expand the servers section -> Databases to view all the available databases.


In this post, you learned how to list all the available databases on a PostgreSQL Server using psql, SQL Queries, and pgAdmin.

Other Cool Stuff

PostgreSQL Default Username and Password
In this brief post, we will cover some methods you can use to configure a password for the default user account in PostgreSQL Server.
PostgreSQL Rename Column
In this tutorial, you will discover how you can rename an existing table column in PostgreSQL.
How to Backup your PostgreSQL Database
Learn how to backup your PostgreSQL database

If you enjoy our content, please consider buying us a coffee to support our work:

More from GeekBits

Join us at GeekBits

Join our members and get a currated list of awesome articles each month.

Great! You’ve successfully signed up.

Welcome back! You've successfully signed in.

You've successfully subscribed to GeekBits.

Success! Check your email for magic link to sign-in.

Success! Your billing info has been updated.

Your billing was not updated.