Show Databases in Postgres: Why SHOW DATABASES Does Not Work

PostgreSQL does not implement SHOW DATABASES. Use the psql meta-command for interactive work, and a query against pg_database when a script needs the list.

This is one of those small friction points that catches everyone arriving from MySQL. You connect to Postgres, type the statement you have typed a thousand times, and get a syntax error that tells you nothing useful about what to type instead.

The short answer is below. The longer answer is worth reading if you are writing anything that consumes the output, because the interactive command and the scriptable query are not the same tool.

Table of contents

• The three ways that work
• Why the MySQL statement does not exist here
• Filtering out the template databases
• Sizes, owners, and encoding in one query
• Getting a clean list for scripts
• Listing tables, schemas, and users while you are here
• How this fits the rest of the stack
• FAQ

The three ways that work

In psql, the meta-command lists every database on the server. The plus variant adds size, tablespace, and description columns.

-- Inside psql
\l
\l+

-- Equivalent long form
\list

From SQL — which works from any client, not just psql — query the catalogue directly.

SELECT datname FROM pg_database WHERE datistemplate = false;

And from the shell without opening an interactive session, pass the command with the -c flag.

psql -U postgres -c "\l"
psql -U postgres -c "SELECT datname FROM pg_database WHERE datistemplate = false;"

Why the MySQL statement does not exist here

MySQL’s SHOW family is a set of bespoke statements returning server metadata. PostgreSQL took a different route: metadata lives in system catalogues and the information schema, and you query it with ordinary SQL.

PostgreSQL does have a SHOW command, but it reports configuration parameters — SHOW work_mem, SHOW search_path. It has nothing to do with listing objects, which is why the error message is unhelpful when you reach for the MySQL form.

There is an architectural point underneath this. In PostgreSQL a connection is bound to a single database, and you cannot query across databases in one session the way MySQL lets you prefix a table with a schema name. Listing databases is genuinely a server-level catalogue lookup, not a query against something you are connected to.

Filtering out the template databases

A default cluster contains template0 and template1, which exist as prototypes for CREATE DATABASE rather than as anything you would connect to. Excluding them is almost always what you want.

-- Real databases only
SELECT datname
FROM pg_database
WHERE datistemplate = false
ORDER BY datname;

-- Only ones that currently accept connections
SELECT datname
FROM pg_database
WHERE datistemplate = false AND datallowconn = true
ORDER BY datname;

The datallowconn check matters more than it looks. template0 is deliberately marked as rejecting connections, and a database can be put in that state during maintenance. Filtering on it means your script does not try to connect to something that will refuse.

Sizes, owners, and encoding in one query

The \l+ meta-command shows size and owner, but the output is formatted for human eyes. For anything programmatic, build the query yourself with the formatting functions.

SELECT
  d.datname                                   AS database,
  pg_catalog.pg_get_userbyid(d.datdba)        AS owner,
  pg_catalog.pg_encoding_to_char(d.encoding)  AS encoding,
  pg_size_pretty(pg_database_size(d.datname)) AS size
FROM pg_database d
WHERE d.datistemplate = false
ORDER BY pg_database_size(d.datname) DESC;

Sorting by actual size rather than the pretty-printed string matters — pg_size_pretty returns text, so ordering by it sorts alphabetically and puts a 9 MB database after an 800 GB one.

Note that pg_database_size requires connect privilege on the target database. On a shared or managed instance, this query can fail on databases you do not own, so handle that rather than assuming every row succeeds.

Getting a clean list for scripts

The default psql output includes column headers, alignment padding, and a row count footer. All of it is noise when the next step is a loop.

# -t drops headers and the footer, -A removes alignment padding
psql -U postgres -tAc \
  "SELECT datname FROM pg_database WHERE datistemplate = false;"

# Practical use: back up every database on the server
for db in $(psql -U postgres -tAc \
  "SELECT datname FROM pg_database WHERE datistemplate = false AND datallowconn = true;")
do
  pg_dump -U postgres -Fc "$db" > "/backups/${db}.dump"
done

Use -tA together and you get one bare name per line, which is exactly what a shell loop wants. Do not parse the output of \l for this — the column formatting is presentation and it is not a stable interface.

If the surrounding tooling speaks JSON, --json on newer psql versions or a row_to_json wrapper in the query itself gives you something structured instead.

Listing tables, schemas, and users while you are here

The same pattern applies to the rest of the catalogue, and these are the meta-commands worth memorising.

• \dt lists tables in the current schema, \dt *.* across all schemas
• \dn lists schemas, \du lists roles and their attributes
• \di lists indexes, \dv views, \df functions
• \c dbname switches the connection to another database
• \d tablename describes a single table’s columns, indexes, and constraints

The corresponding SQL lives in the information schema when you need it in a script: information_schema.tables, information_schema.columns, and so on. Those views are standardised across databases, which makes them the better choice if the same tooling has to work against more than one engine.

How this fits the rest of the stack

Most of the time you reach for this command you are orienting yourself on an unfamiliar instance — which database is which, what is big, who owns what. That is easier when the database is provisioned alongside the service that uses it rather than inherited from somewhere. RunxBuild managed databases come with connection details, backups, and user management in the same dashboard as the deploy, and the RunxBuild hosting calculator shows the database as its own line item so the storage cost is visible before it becomes a surprise.

Useful related references:

• postgres show users: The Three Commands That Work When \\du Doesn’t
• Postgres View Users: The psql Commands and Queries That Actually Show Roles
• Postgres-XL: What the Distributed Fork Was, and Why It Lost to Citus
• Databases on RunxBuild

FAQ

Why does SHOW DATABASES fail in PostgreSQL?

Because PostgreSQL never implemented it. That statement is MySQL syntax. PostgreSQL exposes metadata through system catalogues queried with ordinary SQL, and through psql meta-commands for interactive use.

What is the SQL equivalent of SHOW DATABASES?

SELECT datname FROM pg_database WHERE datistemplate = false; — this works from any client, unlike the psql meta-command which only exists inside psql itself.

How do I list databases without entering psql interactively?

Pass the command with -c, and add -tA for clean output: psql -U postgres -tAc "SELECT datname FROM pg_database WHERE datistemplate = false;" gives one name per line with no headers.

What are template0 and template1?

Prototype databases that CREATE DATABASE copies from. template1 is the default source and can be customised; template0 is a pristine fallback that rejects connections. Filter both out with WHERE datistemplate = false.

How do I see the size of each database?

Use \l+ in psql, or query pg_size_pretty(pg_database_size(datname)). Sort by the raw pg_database_size rather than the pretty string, since the formatted version sorts alphabetically.