PostgreSQL list databases

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. The sections below cover filtering, sizes, shell usage, and checking whether a specific database exists.

Using \l in psql

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

\l

Output:

                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
 myapp     | postgres | 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
(4 rows)

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 two extra columns: size on disk and an optional description.

\l+

Output:

                                                                List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    | Access privileges |  Size   | Tablespace |            Description
-----------+----------+----------+-------------+-------------+-------------------+---------+------------+------------------------------------
 myapp     | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |                   | 142 MB  | pg_default |
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |                   | 8609 kB | pg_default | default administrative connection database
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres      +| 8441 kB | pg_default | unmodifiable empty database
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres      +| 8441 kB | pg_default | default template for new databases
(4 rows)

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 from the shell:

psql -l

With connection options:

psql -h localhost -U postgres -l

This prints the same table as \l and exits immediately. It is useful in scripts or when you want a quick look without starting a session.

Querying pg_database

The pg_database system catalog stores one row per database. You can query it with SQL from any connection.

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 actually 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 the total size in bytes. Wrap it in pg_size_pretty() for a human-readable result.

Checking if a database exists

Use EXISTS with pg_database to get a boolean result. This is useful in scripts that need to check before creating or connecting.

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

Output:

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

Connecting to a database

Once you know the database name, connect with \c in psql:

\c myapp

Output:

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

From the shell, include the database name in the connection string:

psql postgresql://postgres@localhost/myapp

Or with separate flags:

psql -h localhost -U postgres -d myapp

Related psql commands

| Command | Description | |---------|-------------| | \dn | List schemas in the current database | | \dt | List tables in the current schema | | \du | List users and roles | | \df | List functions | | \d tablename | Describe a specific table |

Quick reference

| Task | Command | |------|---------| | List all databases (psql) | \l | | List with sizes (psql) | \l+ | | List matching a pattern (psql) | \l myapp* | | List from the shell | psql -l | | List via SQL | SELECT datname FROM pg_database | | Exclude template databases | Add WHERE datistemplate = false | | Check if a database exists | SELECT EXISTS (SELECT 1 FROM pg_database WHERE datname = 'name') | | Get database size | SELECT pg_size_pretty(pg_database_size('name')) | | Connect to a database | \c dbname |