List databases in PostgreSQL

When working with PostgreSQL, it's common to need a list of all databases present on a server. Unlike MySQL, which offers a straightforward SHOW DATABASES command for this purpose, PostgreSQL provides two main alternatives for listing databases: the psql command-line utility and a SQL SELECT query from a system catalog. Each method is suited to different scenarios and preferences.

Using the psql Command

The psql command-line interface for interacting with PostgreSQL servers is a powerful tool that includes the capability to list all databases. When you're logged into psql, you can use the \l or \list command to display all databases along with their owners, encoding, and other properties:

\l

or

\list

This command is simple and straightforward, making it an excellent choice for quickly checking the available databases directly from the psql prompt. It's particularly useful for database administrators and developers who are already working within the psql environment and need to switch between databases or check database details on the fly.

Using a SELECT Query

For those who prefer working within SQL or need to incorporate database listing into scripts, PostgreSQL allows querying the pg_database system catalog to get information about databases. The pg_database catalog contains metadata about the databases in the PostgreSQL server. You can execute a SELECT query to retrieve the list of database names:

SELECT datname FROM pg_database;

This query returns the names of all databases present on the PostgreSQL server. It's a versatile approach that can be run from any SQL client or integrated into applications that need to programmatically retrieve information about the server's databases.

Choosing the Right Method

The choice between using the psql command and a SQL SELECT query depends on your working environment and requirements. The psql command is quick and easy for interactive use, while the SELECT query offers flexibility for scripts and applications. Both methods provide essential functionality for database management and interaction in PostgreSQL, accommodating different workflows and preferences.