How to list databases in PostgreSQL

Quick Answer

In psql, run \l (or \list):

\l

From SQL, for example in an app or a GUI, query the pg_database catalog:

SELECT datname FROM pg_database WHERE NOT datistemplate ORDER BY datname;

List databases in psql

\l prints one row per database with its owner, encoding, collation and access privileges. It works from any database you're connected to, because the database list is shared by the whole server.

postgres=# \l
                          List of databases
   Name    |  Owner   | Encoding | Locale Provider |  Collate   | ...
-----------+----------+----------+-----------------+------------+----
 myapp     | postgres | UTF8     | libc            | en_US.utf8 |
 postgres  | postgres | UTF8     | libc            | en_US.utf8 |
 template0 | postgres | UTF8     | libc            | en_US.utf8 |
 template1 | postgres | UTF8     | libc            | en_US.utf8 |

You can also list databases without opening a session. psql -l connects, prints the list and exits:

psql -h localhost -U postgres -l

List databases with a SQL query

\l is a psql shortcut, so it fails in pgAdmin, DBeaver, application code and most other clients. Use the pg_database system catalog there instead. \l reads the same catalog behind the scenes.

-- names only, without template0 and template1
SELECT datname
FROM pg_database
WHERE NOT datistemplate
ORDER BY datname;

-- with owner and encoding, like \l
SELECT d.datname,
       pg_get_userbyid(d.datdba)          AS owner,
       pg_encoding_to_char(d.encoding)    AS encoding
FROM pg_database d
WHERE NOT d.datistemplate
ORDER BY 1;

List databases by size

To find which database is using the disk, sort by pg_database_size():

SELECT datname,
       CASE WHEN has_database_privilege(datname, 'CONNECT')
            THEN pg_size_pretty(pg_database_size(datname))
            ELSE 'no access' END AS size
FROM pg_database
WHERE datallowconn
ORDER BY CASE WHEN has_database_privilege(datname, 'CONNECT')
              THEN pg_database_size(datname) END DESC NULLS LAST;

datallowconn leaves out template0, which doesn't accept connections. The CASE matters for anyone who isn't a superuser: without it, one database you can't connect to makes the whole query fail (see below).

What postgres, template0 and template1 are

A fresh server always has these three, and none of them is your data:

• postgres is an empty default database, so tools and admins have somewhere to connect. It's safe to keep and use for admin work.
• template1 is copied every time you run CREATE DATABASE. Anything you add to it, such as an extension, appears in every new database.
• template0 is a pristine copy that can't be changed. Use it with CREATE DATABASE ... TEMPLATE template0 when you need a different encoding or locale.

Don't drop them. Filter them out with WHERE NOT datistemplate instead.

Use the list in a shell script

psql -l output is padded and wrapped, so it's awkward to parse. For scripts, run the query in unaligned, tuples-only mode with -At, which prints one bare name per line:

for db in $(psql -U postgres -Atc "SELECT datname FROM pg_database WHERE NOT datistemplate"); do
  echo "backing up $db"
  pg_dump -U postgres -Fc "$db" > "$db.dump"
done

Which database you are connected to

Listing databases doesn't switch to one. Check where you are and move with \c:

SELECT current_database();
\c myapp

You'll see every database in the list, even ones you can't open. has_database_privilege(datname, 'CONNECT') tells you which ones you can.

Frequently Asked Questions

What is the PostgreSQL equivalent of SHOW DATABASES?

In psql it is \l. In SQL, run SELECT datname FROM pg_database WHERE NOT datistemplate. PostgreSQL has no SHOW DATABASES statement.

How do I list databases without logging into psql?

Run psql -l with your usual connection flags, for example psql -h localhost -U postgres -l. It prints the list and exits.

Why can I see databases I cannot connect to?

pg_database is readable by every role, so the list shows all databases. Whether you can open one depends on the CONNECT privilege, which has_database_privilege() checks.

How do I see the size of each database?

Use \l+ in psql, or the size query on this page. Wrap pg_database_size() in a has_database_privilege() check, or one database you cannot connect to makes the whole query fail.

Related Guides

• List tables