PostgreSQL List Databases: The `\l` Command and Every Alternative

You just connected to a Postgres server, muscle memory kicks in from years of MySQL, and you type SHOW DATABASES;. Postgres responds with a syntax error. This trips up almost everyone coming from MySQL, and it's worth understanding why before you memorize the fix.

The short answer: in psql, type \l (or the longer form \list). That's it. If you need the SQL-standard equivalent for scripts or other clients, query pg_database. If you're not even inside psql yet, psql -l lists databases after connecting to the postgres database.

The rest of this article covers all three methods in depth, what the output columns actually mean, and how to handle the situations where the obvious approach doesn't work — no active connection, restricted permissions, or a context where you need this piped into a script rather than eyeballed on a terminal.

Why There's No SHOW DATABASES; in Postgres

MySQL has SHOW as a dedicated statement family — SHOW DATABASES, SHOW TABLES, SHOW COLUMNS, and so on. It's a MySQL-specific extension, not part of the SQL standard.

Postgres takes a different design stance: administrative and introspection tasks go through two channels instead of one. The first is meta-commands — client-side shortcuts built into psql itself, always prefixed with a backslash (\l, \dt, \du, \c). These aren't SQL. psql intercepts them before anything reaches the server and translates them into the appropriate query (or in some cases, a client-side action like reconnecting). The second channel is system catalogs — ordinary tables and views (pg_database, information_schema.tables, etc.) that you query with regular SQL, from any client, in any language.

This split matters practically. \l only works inside psql. If you're connecting from a Python script with psycopg, a Node app, DBeaver, or any driver that isn't psql, backslash commands don't exist for you — you query the catalog directly. Once you internalize that Postgres separates "convenience shortcuts for the interactive client" from "the actual queryable metadata," the rest of Postgres's introspection tooling (\d, \dt, \du) stops feeling arbitrary and starts feeling consistent.

Method 1: The \l Meta-Command (Fastest, Interactive)

Once you're inside a psql session, this is the command you want:

\l

Or the equivalent long form:

\list

Example output:

                                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |        Access privileges
-----------+----------+----------+-------------+-------------+-----------------------------------
 app_dev   | app_user | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 app_test  | app_user | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 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
(5 rows)

For more detail per database — size on disk, tablespace, and description — add a +:

\l+

This appends Size, Tablespace, and Description columns. It's useful when you're hunting for which database is consuming disk space on a shared server.

Connecting First

When connecting to PostgreSQL via psql, you typically need to establish a connection to a specific database first. Typically you connect like this:

psql -U your_username -h your_host -d postgres

The -d postgres part is a convention — you're connecting to the default postgres maintenance database as an entry point. Once connected, \l shows every database on the server, not just the one you're actively querying. That distinction matters: your connection is scoped to one database for table queries and transactions, but metadata commands like \l see the entire server.

If you don't remember which database to connect to, connect to postgres, run \l, then use \c another_db to switch.

Method 2: Query pg_database Directly (Portable SQL)

\l is a psql convenience. Under the hood, it queries the system catalog table pg_database, which stores one row per database. If you're in a GUI client, an application's SQL console, or you need portability across clients and drivers, run the query yourself:

SELECT datname AS name,
       pg_catalog.pg_get_userbyid(datdba) AS owner,
       pg_encoding_to_char(encoding) AS encoding,
       datcollate AS collate,
       datctype AS ctype
FROM pg_database
ORDER BY datname;

This query works identically in psql, Python with psycopg, Node with pg, DBeaver, or any other Postgres driver — because it's standard SQL against a real table, not a client-specific shortcut.

A simpler version if you just want names:

SELECT datname FROM pg_database WHERE datistemplate = false;

The datistemplate = false filter excludes template0 and template1, the built-in templates Postgres clones when you run CREATE DATABASE. They're real entries, so they appear in unfiltered queries, but you'll rarely need them when auditing "what databases exist for my application."

Method 3: The Non-Interactive One-Liner (Scripting)

Sometimes you don't want a psql session at all — you want one command from a shell script, a CI pipeline, or a cron job that prints the list and exits. Two equivalent options:

psql -U your_username -h your_host -d postgres -c "\l"

Or:

psql -U your_username -h your_host -l

Both connect, run \l, print the result, and disconnect — no interactive prompt.

For scripted parsing (feeding into a loop or grep check), combine psql's quiet flags with the SQL query from Method 2:

psql -U your_username -h your_host -d postgres -t -c "SELECT datname FROM pg_database WHERE datistemplate = false;"

The -t flag strips headers and formatting, so you get one database name per line.

Reading the Output Columns

Whether you used \l or the equivalent SQL query, the columns mean the same thing:

Two points worth internalizing. First, Collate and Ctype are baked in at creation — they're not read-once-and-forget, because they affect how text sorts and matches. If sort order looks "wrong" for accented characters or mixed-case strings, check these settings.

Second, an empty Access privileges field is normal — it doesn't mean "no one can access this," it means "no privileges have been customized away from the default." Non-empty values on template0 and template1 are expected, since Postgres locks them down to prevent accidental modification.

When Permissions Get in the Way

If \l or the pg_database query returns fewer databases than expected, permissions may be involved. Role-level access controls can affect which databases appear in query results. Without confirmation of the specific row-visibility mechanism, it's worth checking whether the connected role has sufficient privileges across all databases you expect to see.

If you suspect this:

• Check which role you authenticated as (\conninfo inside psql shows your connection details).
• Ask whoever administers the server to confirm your role's privileges, or connect with a superuser account temporarily to get the full list.
• Remember that seeing a database in \l doesn't guarantee you can connect to it or read its tables — visibility of the database entry and access to its contents are separate concerns.

Quick Reference

• Inside psql, interactive: \l (or \l+ for size and description)
• Portable SQL, any client: SELECT datname FROM pg_database WHERE datistemplate = false;
• From the shell, non-interactive: psql -U user -h host -l
• Scripted, names only: psql -U user -h host -d postgres -t -c "SELECT datname FROM pg_database WHERE datistemplate = false;"

Pick based on context: interactive session, automated script, or a client that doesn't speak psql's backslash dialect. All three read from the same place.

FAQ

Why doesn't SHOW DATABASES; work in Postgres? SHOW DATABASES is MySQL-specific, not part of the SQL standard. Postgres exposes the same information through a psql meta-command (\l) and through the standard system catalog (pg_database).

What's the difference between \l and \l+? \l shows Name, Owner, Encoding, Collate, Ctype, and Access privileges. \l+ adds Size, Tablespace, and Description.

Do I need to be connected to a specific database to list all databases? Yes — Postgres requires every connection to target a specific database, commonly postgres as a default entry point. Once connected, \l and pg_database show every database on the server.

Why do template0 and template1 show up in the list? They're the built-in templates Postgres clones when you run CREATE DATABASE. Add WHERE datistemplate = false to your query to exclude them.

Can I list databases without using psql at all? Yes. Any Postgres client or driver can run SELECT datname FROM pg_database; as ordinary SQL. \l is just a psql-specific shortcut for that query.