How to List Databases in PostgreSQL | Complete Guide

PostgreSQL, a powerful open-source relational database system, provides various methods to list the databases within a PostgreSQL server. Whether using the command line interface or graphical tools, listing databases is a fundamental task that can help you manage and organize your database environment effectively.

Using the PostgreSQL Command Line Interface (psql)

One of the most common ways to list databases in PostgreSQL is using the psql command line interface. Here’s how you can do it:

Connect to PostgreSQL Server

First, you need to connect to your PostgreSQL server using the psql command:

psql -U username -h hostname -d database

Replace username, hostname, and database with your PostgreSQL credentials.

List Databases

Once you are connected, you can list all databases by executing the following command:

\l
Or you can use:
\list

Using SQL Query

You can also list all databases by executing an SQL query. This can be done within any SQL client connected to your PostgreSQL server:

SELECT datname FROM pg_database;

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

Using pgAdmin

pgAdmin is a popular graphical tool for managing PostgreSQL databases. Here’s how you can list databases using pgAdmin

Open pgAdmin and Connect

Launch pgAdmin and connect to your PostgreSQL server.

View Databases

In the Browser pane, expand the server node, and you will see a list of all databases under the Databases node. Here’s an example of how you can connect to a PostgreSQL server and list databases using Python with the psycopg2 library

import psycopg2
# Connect to PostgreSQL server
conn = psycopg2.connect(
    host="hostname",
    database="database",
    user="username",
    password="password"
)
# Create a cursor object
cur = conn.cursor()
# Execute the SQL query to list databases
cur.execute("SELECT datname FROM pg_database;")
# Fetch and print the results
databases = cur.fetchall()
for database in databases:
    print(database[0])
# Close the cursor and connection
cur.close()
conn.close()

Replace hostname, database, username, and password with your PostgreSQL credentials.

Frequently Asked QuestionsHow can I list databases using a graphical tool?

You can use pgAdmin, a popular graphical tool for PostgreSQL. After connecting to your server in pgAdmin, you will see a list of databases in the Browser pane.

What permissions do I need to list databases?

You need to have the CONNECT privilege on the PostgreSQL server. Typically, a database user with normal privileges can list databases.

Can I filter the list of databases?

Yes, you can modify the SQL query to filter the list of databases. For example, to list databases that start with the letter ‘t’:

SELECT datname FROM pg_database WHERE datname LIKE ‘t%’;

Conclusion

Listing databases in PostgreSQL is a straightforward process that can be done using various methods, including the psql command line interface, SQL queries, and graphical tools like pgAdmin. By understanding how to list databases, you can better manage and navigate your PostgreSQL environment