PostgreSQL list databases (and the SHOW DATABASES equivalent)

Quick answer

To list all databases in psql:

\l

Or with SQL:

SELECT datname FROM pg_database;

Both work from any database you are connected to. If you came here looking for SHOW DATABASES, that is a MySQL command and it does not exist in PostgreSQL. The section below explains the error it gives you.

Why SHOW DATABASES does not work in PostgreSQL

Type the MySQL command into psql and you get something confusing:

SHOW DATABASES;

ERROR:  unrecognized configuration parameter "databases"

That error never mentions databases, which is why it sends people searching.

The reason is that SHOW is a real PostgreSQL command, it just does something else. It displays configuration parameters:

SHOW work_mem;

 work_mem
----------
 4MB
(1 row)

So PostgreSQL parses SHOW DATABASES as a request for a configuration parameter named databases, finds no such setting, and reports exactly that. It is not a syntax error, because the syntax is fine. The parameter is what does not exist.

Use \l instead.

Coming from MySQL

The whole family of MySQL commands maps onto psql meta-commands:

The backslash commands are psql features, not SQL. They will not work from an application driver or a GUI query editor. For those, query the catalog directly with the SQL further down this page.

If you are going the other way, see MySQL SHOW DATABASES.

Using \l in psql

The \l meta-command lists every database on the server along with its owner, encoding, and locale settings.

\l

Output:

                                                    List of databases
   Name    |  Owner   | Encoding | Locale Provider |  Collate   |   Ctype    | Locale | ICU Rules |   Access privileges
-----------+----------+----------+-----------------+------------+------------+--------+-----------+-----------------------
 myapp     | postgres | UTF8     | libc            | en_US.utf8 | en_US.utf8 |        |           |
 postgres  | postgres | UTF8     | libc            | en_US.utf8 | en_US.utf8 |        |           |
 template0 | postgres | UTF8     | libc            | en_US.utf8 | en_US.utf8 |        |           | =c/postgres          +
           |          |          |                 |            |            |        |           | postgres=CTc/postgres
 template1 | postgres | UTF8     | libc            | en_US.utf8 | en_US.utf8 |        |           | =c/postgres          +
           |          |          |                 |            |            |        |           | postgres=CTc/postgres
(4 rows)

\list is a longer alias for the same thing, if you prefer readable scripts.

The Locale Provider, Locale, and ICU Rules columns are newer additions (PostgreSQL 15 and 16). On PostgreSQL 14 and earlier you will see a shorter table without them.

template0 and template1 are system databases PostgreSQL uses as templates when creating new databases. You can ignore them in most cases.

\l+ for size and description

Adding + includes size on disk and an optional description.

\l+ postgres

Output:

   Name   |  Owner   | Encoding | ... | Access privileges |  Size   | Tablespace |                Description
----------+----------+----------+-----+-------------------+---------+------------+--------------------------------------------
 postgres | postgres | UTF8     | ... |                   | 7678 kB | pg_default | default administrative connection database
(1 row)

Filter by name pattern

Pass a pattern to \l to show only matching databases. The pattern supports * as a wildcard.

-- Databases starting with "myapp"
\l myapp*

-- Databases containing "staging"
\l *staging*

psql -l from the shell

To list databases without entering psql's interactive mode, use the -l flag:

psql -l

With connection options:

psql -h localhost -U postgres -l

This prints the same table as \l and exits immediately.

Listing databases in a script

The formatted table is awkward to parse. For scripts, use -A (unaligned) and -t (tuples only) to get bare values:

psql -U postgres -Atc "SELECT datname FROM pg_database WHERE datistemplate = false;"

myapp
postgres

Or with -l, using -q and -t and cutting the first column:

psql -lqt | cut -d'|' -f1 | sed '/^\s*$/d'

The -Atc form is easier to reason about, because you control the query.

Listing databases in Docker

If Postgres is running in a container, run psql inside it:

docker exec -it postgres psql -U postgres -c '\l'

This connects over the container's Unix socket, so it does not need a password. See Postgres Docker: The Complete 2026 Guide for the rest of the setup.

Querying pg_database

The pg_database system catalog stores one row per database. Query it with SQL from any connection, including from an application driver where \l is unavailable.

List all databases

SELECT datname FROM pg_database ORDER BY datname;

Exclude template databases

SELECT datname
FROM pg_database
WHERE datistemplate = false
ORDER BY datname;

The datistemplate flag is true for template0 and template1. Filtering it out gives you only the databases your team created.

Include owner and encoding

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

Include database size

SELECT
  datname AS database,
  pg_size_pretty(pg_database_size(datname)) AS size
FROM pg_database
WHERE datistemplate = false
ORDER BY pg_database_size(datname) DESC;

Output:

  database  |  size
------------+---------
 myapp      | 142 MB
 analytics  | 38 MB
 postgres   | 8609 kB
(3 rows)

pg_database_size() accepts the database name and returns bytes. Wrap it in pg_size_pretty() for a readable result.

Can every user see every database?

Yes, and you cannot prevent it by revoking CONNECT.

This surprises people, and a lot of writing on the subject gets it wrong, so it is worth demonstrating. pg_database is a shared catalog with SELECT granted to PUBLIC:

SELECT relacl FROM pg_class WHERE relname = 'pg_database';

{postgres=arwdDxtm/postgres,=r/postgres}

The =r/postgres entry is the important one. An empty grantee means PUBLIC, and r means SELECT. Every role can read that catalog.

Revoking CONNECT does not change that:

CREATE ROLE lowpriv LOGIN;
CREATE DATABASE secret_db;
REVOKE CONNECT ON DATABASE secret_db FROM PUBLIC;

Connect as lowpriv and the database is still listed:

SELECT datname, has_database_privilege(current_user, datname, 'CONNECT')
FROM pg_database WHERE datname = 'secret_db';

 secret_db | f

Visible, and not connectable. Trying to connect confirms it:

psql: error: connection to server at "127.0.0.1", port 5432 failed: FATAL:  permission denied for database "secret_db"
DETAIL:  User does not have CONNECT privilege.

So visibility and CONNECT are unrelated. REVOKE CONNECT stops people using a database; it does not hide that it exists. If a database name is itself sensitive, revoking privileges is not the answer. Put it on a separate cluster.

Checking if a database exists

Use EXISTS with pg_database for a boolean result. Useful in scripts that check before creating or connecting.

SELECT EXISTS (
  SELECT 1
  FROM pg_database
  WHERE datname = 'myapp'
);

Output:

 exists
--------
 t
(1 row)

From a shell, the exit code is usually more useful than the output:

psql -U postgres -tc "SELECT 1 FROM pg_database WHERE datname='myapp'" | grep -q 1 \
  && echo "exists" || echo "missing"

Connecting to a database

Once you know the name, connect with \c:

\c myapp

You are now connected to database "myapp" as user "postgres".

Note that \c opens a new connection rather than switching within your session. See connecting to a PostgreSQL database for the details, including connection strings and the MySQL USE equivalent.

Common problems

psql: command not found

psql is not on your PATH. On macOS with Homebrew, brew install libpq then add it to your path. On Windows, the installer does not add psql to PATH by default, which produces:

The term 'psql' is not recognized as the name of a cmdlet, function, script file, or operable program.

Add PostgreSQL's bin directory (something like C:\Program Files\PostgreSQL\18\bin) to your PATH, or use the SQL Shell shortcut the installer creates.

\l works but my application cannot list databases

Backslash commands are psql features. Application drivers do not understand them. Query pg_database instead.

The database I just created is not listed

Check you are connected to the right server, not the right database. \l shows every database in the cluster regardless of which one you are connected to, so if it is missing, you are talking to a different server. \conninfo will tell you where you actually are.

Related psql commands

Quick reference