January 15, 2023 By Matthew Rathbone *

To list all databases in Postgres using psql, you can use the \l command. This command will display a table with information about each database, including the database name, the owner of the database, the encoding for the database, and the collation for the database.

To use the \l command, connect to the Postgres database server using the psql command-line interface. Once you are connected, type \l and press Enter. This will display a list of all databases on the server.

Make PostgreSQL Fun Again With Beekeeper Studio

Write SQL, create tables, edit data, and have fun doing it! Beekeeper Studio is available for MacOS, Linux, and Windows.

Use \list or \l+ to get more information

Alternatively, you can use the \list or \l+ command to list databases in Postgres. These commands will provide additional information about each database, such as the size of the database and the date it was created.

Listing databases you have access to

To list only the databases that you have access to, you can use the \du command. This command will show a list of all roles (including databases) and the privileges that each role has.

View more detailed information for a database

If you want to see more detailed information about a specific database, you can use the \db command followed by the name of the database. This will display information about the tables, functions, and other objects in the database.

Also available in the system catalog

In addition to using the command-line interface, you can also list databases in Postgres using the pg_catalog.pg_database system catalog. This catalog contains a row for each database in the system, and you can query it to retrieve information about the databases.

For example, the following query will list all databases in Postgres:

SELECT datname FROM pg_catalog.pg_database;

This query will return a list of database names. You can then use this information to access the databases and perform other operations on them.

Just point and click with Beekeeper Studio

Beekeeper Studio lists all databases in a dropdown at the top of the interface, switch between databases at any time. This is much easier than remembering SQL.

PostgreSQL listing databases summary

In summary, to list databases in Postgres, you can use the \l, \list, or \l+ commands in the psql command-line interface, or you can query the pg_catalog.pg_database system catalog. These methods will provide you with a list of databases on the server, along with information about each database.