PostgreSQL is a powerful, open-source relational database management system used widely by developers and database administrators. Understanding how to list all databases in your PostgreSQL instance is crucial for database management and administration.

In this tutorial, we will walk you through various methods to view all databases present in a PostgreSQL instance.

Prerequisites

Before we dive into the details, make sure you have the following:

  • PostgreSQL is installed on your system.
  • Access to the PostgreSQL command line.

1. Using the psql Command Line Interface

The psql command line interface is a powerful tool to interact with your PostgreSQL database. Here’s how you can list all databases using psql.

1. First, you need to access the psql command line. Open your terminal and connect to your PostgreSQL server by running:

sudo -u postgres psql

Replace postgres with your PostgreSQL username. You will be prompted to enter your password.

2. Once you are logged in, you can list all databases by using the following command:

\l

Alternatively, you can use:

\list

Output:

                                  List of databases
    Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
------------+----------+----------+-------------+-------------+-----------------------
 mydatabase | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 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

This output lists all databases along with their owners, encoding, collation, ctype, and access privileges.

2. Using SQL Queries

You can also use a SQL query to list all databases. This method can be handy if you prefer to use SQL commands directly.

1. Connect to your PostgreSQL server using the psql command line as described earlier.

2. Run the following SQL query to list all databases:

SELECT datname FROM pg_database;

Output:

  datname   
------------
 postgres
 mydatabase
 template1
 template0
(4 rows)

This query retrieves the names of all databases from the pg_database system catalog.

Conclusion

In this article, we explored several methods for listing all databases in PostgreSQL. We covered how to use the psql command line interface and execute SQL queries to view all databases. Each method offers a different approach, allowing you to choose the one that best suits your workflow. You can now test this tutorial on dedicated server hosting from Atlantic.Net!