How to Show Databases in PostgreSQL

Key Takeaways

Use \l or \l+ commands in psql to list databases.
Query the pg_database system catalog to view databases via SQL.
Graphical tools like pgAdmin and DBeaver provide an easier visual option.

Unlike MySQL, PostgreSQL does not support the SHOW DATABASES SQL command. However, there are several effective methods to list all databases on a PostgreSQL server, whether you're using the command-line interface, SQL queries, or graphical tools. (PostgreSQL – Show Databases | GeeksforGeeks)

1. Using the `psql` Command-Line Interface

The psql tool is PostgreSQL's interactive terminal. After connecting to your PostgreSQL server, you can list all databases using the following meta-commands: (Documentation: 17: 1.4. Accessing a Database - PostgreSQL, How to List databases and tables in PostgreSQL using psql - Atlassian)

List all databases:
```
\l
```

This command displays a list of all databases along with details such as their owners, encoding, collation, and access privileges. (How to List Databases Using the psql command line tool)

List all databases with additional information:
```
\l+
```

This provides extra details, including the size of each database and any associated comments. (How to List Databases Using the psql command line tool)

Alternatively, from your system's terminal, you can execute:

psql -U <username> -l

Replace <username> with your PostgreSQL username. This command connects to the server and lists all databases. (How To List Databases in PostgreSQL: A Complete Guide)

2. Using SQL Queries

You can also retrieve a list of databases by querying the pg_database system catalog:

SELECT datname FROM pg_database WHERE datistemplate = false;

This SQL statement returns the names of all databases, excluding the default template databases (template0 and template1). (How to List databases and tables in PostgreSQL using psql - Atlassian)

3. Using Graphical Tools

For those who prefer graphical interfaces, tools like pgAdmin, DBeaver, and DbVisualizer offer user-friendly ways to view databases: (Three Easy Ways to List Databases in PostgreSQL - Medium)

pgAdmin:
1. Open pgAdmin and connect to your PostgreSQL server.
2. In the left-hand tree, expand the "Servers" node.
3. Under your server, expand the "Databases" node to see all available databases. (How To List Databases in PostgreSQL: A Complete Guide, How to List All Databases in PostgreSQL (psql and More) - StrongDM)
DBeaver:
1. Launch DBeaver and create a new database connection.
2. Select "PostgreSQL" as the database type.
3. Enter your connection details and enable the "Show all databases" option.
4. Once connected, expand the "Databases" node to view all databases. (How to List All Databases in PostgreSQL (psql and More) - StrongDM, How to List All Databases in PostgreSQL {3 Methods} - phoenixNAP)
DbVisualizer:
1. Open DbVisualizer and connect to your PostgreSQL server.
2. In the "Databases" tab, you'll see a list of all databases on the server. (How to List All Databases in PostgreSQL {3 Methods} - phoenixNAP)

Summary

Use \l or \l+ in psql to list all databases.
Execute SELECT datname FROM pg_database WHERE datistemplate = false; to retrieve database names via SQL.
Utilize graphical tools like pgAdmin, DBeaver, or DbVisualizer for a visual representation of your databases. (PostgreSQL Show Databases - Neon, How to List All Databases in PostgreSQL (psql and More) - StrongDM)

By employing these methods, you can effectively view and manage the databases within your PostgreSQL server.