How to show PostgreSQL databases using psql and GUI tools

When working with multiple PostgreSQL databases at the same time, it is essential to know how to list them. It can be necessary when you are getting familiar with a new environment or performing server maintenance.

In this article, we are going to provide you with an illustrated guide on how to show all databases in PostgreSQL, filter them, and order them according to certain criteria. Keep reading to find out about:

How to show databases from the command line
What is the basic syntax
How to filter the database list
How to sort the database list
What permissions are required to get the list of databases
How to get and manage a database list with a PostgreSQL GUI tool

Listing databases in psql

SQL Shell (psql) is a default command-line solution that allows you to interact with a PostgreSQL database. Among other options, you can use it to list all databases. We will be using it as an instrument to demonstrate various examples in the upcoming sections of this article:

The \l or \list command.
The \l+ or \list+ command.
The SELECT statement with pg_database catalog.

Understanding PostgreSQL database listing

On executing either \l or \list, you will get a list of all available databases in the PostgreSQL server, along with their names, owners, corresponding encodings, collation order, character classification, and permissions.

To see even more information on databases, use \l+ or \list+ command, either of which provides more comprehensive details, particularly database size, tablespace, and description.

As to the SELECT syntax, the following query will fetch you the names of all databases in the pg_database catalog:

SELECT datname FROM pg_database;

Permissions required for listing databases

By default, any PostgreSQL user can see a list of all databases they own or those on which they have privileges. Unlike them, superusers can see all the databases along with their details.

The \du command displays the list of roles in the PostgreSQL database along with their associated privileges.

Either the owner or a superuser can adjust the privileges for others using a carrot and a stick: GRANT or REVOKE commands, depending on the situation.

Adjust permissions for listing databases in PostgreSQL

Managing databases with dbForge Studio

dbForge Studio for PostgreSQL allows you to display and manage the list of databases in two different ways:

After you have connected to the server, the databases that are hosted on it will be displayed in Database Explorer. To view the database tables, simply expand the database and table nodes.
Querying the pg_database catalog we have mentioned above will work just as well in the Studio's Code Editor. It is equipped with impeccable syntax check and context-sensitive code completion.

dbForge Studio for PostgreSQL - manage and see all databases on Windows

Filtering and sorting database lists

To single out particular databases from a broad list, you can use the LIKE expression along with the SELECT command when querying pg_database. Suppose we need to get the list of all databases that start with B:

SELECT
  datname
FROM pg_database
WHERE datname LIKE 'b%';

To sort the database names, we can use the ORDER BY clause:

SELECT *
FROM pg_database 
WHERE datname LIKE 's%'
ORDER BY datconnlimit;

Conclusion

In today's illustrated guide, we have been talking about the different ways to list all the databases in PostgreSQL. Whether you prefer command-line tools or opt for a more user-friendly GUI like dbForge Studio, this task should not be an issue anymore. It can simplify the whole process and offer enhanced management capabilities for your daily database management routine. To experience the power firsthand, consider downloading a free, fully functional trial of dbForge Studio for PostgreSQL.