Three Easy Ways to List Databases in PostgreSQL

Listing databases in PostgreSQL is a common task for developers and DBAs. This article explores three methods for achieving this: command-line tools, SQL queries, and database clients.

Listing Databases with Command-Line Tool

To list databases using the psql command-line tool, follow these steps:

Connect to the PostgreSQL server, replace <username> with your PostgreSQL username. Enter your password when prompted.

psql -U <username>

List the databases, for more details, use:

\\l

\\l+

You can also list databases by querying the pg_catalog.pg_database table like:

SELECT * 
FROM pg_catalog.pg_database 
WHERE datistemplate = false;

This query excludes template databases.

Viewing Databases in a Client

Database clients like DbVisualizer provide a visual way to manage your PostgreSQL databases:

1. Connect to your PostgreSQL server.
2. Navigate to the “Databases” section to see the list of databases.

FAQ

How to list PostgreSQL databases with a single command? Use:

psql -U <username> -l

This connects to the server and lists the databases.

How to get the list of tables in a database with psql? First, connect to the database:

\\c <database_name>

Then, list the tables:

\\dt

What is the easiest way to list databases in PostgreSQL? Using a database client like DbVisualizer for a user-friendly interface.

How to use pgAdmin to view the list of databases? Open pgAdmin, connect to your server, and expand the “Databases” group to see the list.

Conclusion

Listing databases in PostgreSQL can be done via command-line, SQL queries, or database clients. Each method has its advantages depending on your needs. For more details, check out the article How to List Databases in Postgres Using psql and Other Techniques.