Different database management systems adopt different approaches to show the list of available databases. For instance, to show the list of databases, the “SHOW DATABASES” statement is used in MySQL and MariaDB. However, this statement is not supported by Postgres. Instead, Postgres offers various other alternative approaches to show the available databases, such as “\l”, "pg_databases" catalog, etc.

This write-up will teach you how to show databases in PostgreSQL via SQL Shell and pgAdmin. So, let’s get started.

How to Show Databases in Postgres Using SQL Shell?

Use the “\l” command from the SQL Shell to get the list of available databases. To achieve this purpose, use the following syntax:

\l

Important: Don not specify a semi-colon at the end of the "\l" meta-command; otherwise, you may encounter an invalid command error.

Step 1: Launch SQL Shell

In order to run the “\l” command, firstly, you need to log into SQL Shell. To do so, launch the SQL Shell(psql) from the Windows start menu:

Clicking on the “open” will show the following interface:

Fill in all the necessary details and hit the “ENTER” button. Consequently, the following window will appear:

Here, “postgres” shows that you are connected to the default database.

Step 2: Show Databases Using \l Command

Once you are connected to the default database, execute the below-given statement to show the list of available databases:

\l

The output authenticates the working of the “\l” command as it shows the list of available databases. Use the “\l+” command to get the list of available databases with more details such as size, tablespace, description, etc.

Step 3: Show Databases Using pg_databses

In PostgreSQL, the pg_databses catalog holds all the details regarding databases. So, you can run the SELECT query in PostgreSQL to show the list of available databases in pg_databases:

SELECT datname FROM pg_database;

Note: You can execute this query from pgAdmin's query tool to get the same result.

How to Show Databases in Postgres Using pgAdmin?

pgAdmin is a feature-rich Postgres administration and development tool that lets us perform different database operations conveniently. We can not execute the "\l" meta-command from pgAdmin, however, we can use the pg_database catalog to get the list of available databases. Also, we can use pgAdmin manually to show the available databases in Postgres.

Follow the below-listed stepwise instructions to get the list of available databases in Postgres via pgAdmin.

Step 1: Launch pgAdmin

Open the pgAmin 4 from the Windows start menu:

Clicking on the “open” will lead you to the following interface:

Provide the password and hit the “OK” button to log into the pgAdmin.

Step 2: Show Databases Using Database Tree

Expand the “Servers” tree located in the top left corner of the pgAdmin:

Click on “Databases” to see the list of databases:

Now select the properties tab to see detailed information about each database:

The above snippet shows detailed information about each database.

Conclusion

In PostgreSQL, the “\l” meta-command and “pg_databases” catalog are used to show the list of databases. The “\l+” command is used to get the list of available databases with more details such as size, tablespace, description, etc. Use the “\l” or “\l+” commands and the “pg_databases” catalog from the SQL Shell to get the list of available databases. While to show the list of databases using pgAdmin, click on the “Servers”, expand the “Databases” Tree, and then click on the “properties” tab. This write-up explained various methods to show databases in Postgres.