Listing PostgreSQL databases from the Command Line

Prerequisites

Before you begin, ensure that:

1. PostgreSQL is installed on your system.
2. You have access to a PostgreSQL user with the necessary permissions (e.g., postgres or a user with administrative privileges).

Step 1: Accessing PostgreSQL from the Terminal

To access the PostgreSQL terminal, open your terminal or command prompt and run the following command:

psql -U [username]

• Replace [username] with your PostgreSQL username (the default is often postgres).
• If a password is required, the system will prompt you to enter it. Type the password and press Enter.

If the connection is successful, you’ll see a PostgreSQL prompt similar to this:

postgres=#

Step 2: Listing Databases

Once connected, use the following command to display all available databases:

\l

This command provides a detailed list of databases, including their names, owners, encoding, collation, and access privileges. For example:

                                    List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 my_db     | user1    | UTF8     | en_US.UTF-8 | en_US.UTF-8 |

• postgres: The default database for administrative purposes.
• template0 and template1: System databases used for creating new databases.
• Custom databases: Any databases you’ve created or used will also appear in this list.

For a simpler, more concise list, run this SQL query:

SELECT datname FROM pg_database;

This will return just the database names:

  datname
-----------
 postgres
 template0
 template1
 my_db
(4 rows)

Step 3: Exiting PostgreSQL

When you’re done, type the following command to exit PostgreSQL:

\q

Pro Tip: Use a One-Liner Command

If you want to list databases without entering the interactive PostgreSQL interface, use this command directly in your terminal:

psql -U [username] -c "\l"

This will immediately display the list of databases without requiring an active session.