How to list databases in terminal in PostgresSQL?

How to List Databases in PostgreSQL from the Terminal

Learn various methods to view available PostgreSQL databases directly from your terminal, using both psql commands and SQL queries.

PostgreSQL is a powerful, open-source relational database system. When working with PostgreSQL, one of the most fundamental tasks is to list the available databases. This is crucial for navigating your database environment, verifying database creation, or simply understanding what's hosted on your server. This article will guide you through the most common and effective ways to achieve this directly from your terminal.

Using the psql Meta-Command

The psql command-line utility is the interactive terminal for PostgreSQL. It provides a rich set of meta-commands (commands starting with a backslash \) that simplify common database operations. The most straightforward way to list databases is by using the \l or \list meta-command.

psql -U your_username
\l

Connect to psql and list databases

After connecting to psql with your username (e.g., psql -U postgres), simply type \l and press Enter. This will display a table showing all databases, their owners, encoding, collation, and access privileges. The \l+ command provides even more detailed information, including size and tablespace.

flowchart TD
    A[Start Terminal] --> B{Connect to psql?}
    B -- Yes --> C[Run: psql -U your_username]
    C --> D[Enter Password (if prompted)]
    D --> E[psql Prompt: \l]
    E --> F[Display Database List]
    B -- No --> G[Run: psql -U your_username -c "\l"]
    G --> F

Workflow for listing databases using psql meta-commands

Querying the pg_database Catalog Table

For a more programmatic approach or when you need to filter or format the output in a specific way, you can query the pg_database system catalog table. This table contains information about all available databases.

SELECT datname FROM pg_database;

-- To exclude system databases:
SELECT datname FROM pg_database WHERE datistemplate = false;

SQL queries to list databases

The datname column holds the name of each database. By default, PostgreSQL includes template databases (template0 and template1) and the postgres database. You can filter these out by adding a WHERE clause, for example, WHERE datistemplate = false to only show user-created databases.

Listing Databases from the Shell (Non-Interactive)

Sometimes you need to list databases as part of a script or without entering the interactive psql session. This can be done by combining the psql command with the -c flag to execute a SQL query or a meta-command directly.

# Using the meta-command directly
psql -U your_username -c "\l"

# Using a SQL query directly
psql -U your_username -c "SELECT datname FROM pg_database;"

Non-interactive commands to list databases

These commands are particularly useful for automation scripts or when you need to quickly check database names without a full psql session. Remember to replace your_username with your actual PostgreSQL username.