PostgresqlHow-ToBeginner · 3 min read

How to List All Databases in PostgreSQL Quickly

To list all databases in PostgreSQL, use the SQL query SELECT datname FROM pg_database; or the psql meta-command \l. Both commands show the names of all databases available in your PostgreSQL server.

📐

Syntax

You can list all databases in PostgreSQL using either a SQL query or a psql meta-command.

SELECT datname FROM pg_database;: This SQL query selects the database names from the system catalog pg_database.
\l: This is a psql client meta-command that lists all databases with additional details like owner and encoding.

sql

SELECT datname FROM pg_database;

\l

💻

Example

This example shows how to list all databases using both the SQL query and the psql meta-command.

sql

postgres=# SELECT datname FROM pg_database;
  datname   
------------
 postgres
 template1
 template0
 mydb
(4 rows)

postgres=# \l
                                   List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-----------+----------+----------+-------------+-------------+-----------------------
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 mydb      | user1    | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
(4 rows)

Output

⚠️

Common Pitfalls

Some common mistakes when listing databases in PostgreSQL include:

Trying to run the psql meta-command \l inside a SQL query window or client that does not support psql commands.
Not having the right permissions to see all databases, which may cause incomplete results.
Confusing the SQL query SELECT datname FROM pg_database; with other system catalogs that do not list databases.

sql

/* Wrong: Using psql meta-command in a non-psql client */
-- This will cause an error in clients like pgAdmin query tool
\l

/* Right: Use SQL query in any SQL client */
SELECT datname FROM pg_database;

📊

Quick Reference

Command	Description
SELECT datname FROM pg_database;	Lists all database names using SQL query.
\l	Lists all databases with details in psql client.
\list	Alias for \l in psql client.
\c database_name	Connects to a specific database.

✅

Key Takeaways

Use SELECT datname FROM pg_database; to list all databases in any SQL client.

Use the psql meta-command \l for a detailed list inside the psql terminal.

psql meta-commands like \l do not work in non-psql clients.

You need proper permissions to see all databases in PostgreSQL.

Remember pg_database is the system catalog storing database info.