Skip to main content
0 reactions

How to List Databases in PostgreSQL

Featured card titled List Databases in PostgreSQL, with a terminal window listing four ticked commands: \l, \l+, \l s* and psql -U postgres -l.
Ways to list the databases on a PostgreSQL server.

One PostgreSQL server can hold many databases. In psql, one short command lists them all, and a SQL query does the same when you only need their names.

Every command and output on this page is real, from PostgreSQL 19 on macOS.

Syntax

Syntax (psql)Plain text
\l[+] [ pattern ]
Syntax (SQL)SQL
SELECT datname FROM pg_database;
  • \l: lists the databases on the server with their owners, encodings and access privileges.
  • +: adds each database’s size, tablespace and description.
  • pattern: optional. Shows only the databases whose names match it; * stands for any characters, so s* means names that start with s.
  • pg_database: a system table with one row per database. Its datname column holds the names.

\l is described in the psql documentation (opens in a new tab), and pg_database in its catalog page (opens in a new tab).

Step 1: Create Two Example Databases

A new server has only its built-in databases, so this guide first creates two small ones, school and shop, to make the lists more interesting. To list your own databases, skip to Step 2. Connect with psql, as Part 2 of the PostgreSQL Tutorial shows, then run:

postgres=#SQL
CREATE DATABASE school;
CREATE DATABASE shop;
OutputPlain text
CREATE DATABASE
CREATE DATABASE

Step 2: List All Databases with \l

postgres=#Plain text
\l
OutputPlain text
                                                List of databases
   Name    |  Owner   | Encoding | Locale Provider | Collate | Ctype | Locale | ICU Rules |   Access privileges
-----------+----------+----------+-----------------+---------+-------+--------+-----------+-----------------------
 postgres  | postgres | UTF8     | libc            | C       | C     |        |           |
 school    | postgres | UTF8     | libc            | C       | C     |        |           |
 shop      | postgres | UTF8     | libc            | C       | C     |        |           |
 template0 | postgres | UTF8     | libc            | C       | C     |        |           | =c/postgres          +
           |          |          |                 |         |       |        |           | postgres=CTc/postgres
 template1 | postgres | UTF8     | libc            | C       | C     |        |           | =c/postgres          +
           |          |          |                 |         |       |        |           | postgres=CTc/postgres
(5 rows)

Each row is one database. postgres is the default database you connect to, and template0 and template1 are templates that PostgreSQL copies when you create a new database.

Step 3: Show Each Database’s Size with \l+

postgres=#Plain text
\l+
OutputPlain text
                                                                                  List of databases
   Name    |  Owner   | Encoding | Locale Provider | Collate | Ctype | Locale | ICU Rules |   Access privileges   |  Size   | Tablespace |                Description
-----------+----------+----------+-----------------+---------+-------+--------+-----------+-----------------------+---------+------------+--------------------------------------------
 postgres  | postgres | UTF8     | libc            | C       | C     |        |           |                       | 7839 kB | pg_default | default administrative connection database
 school    | postgres | UTF8     | libc            | C       | C     |        |           |                       | 7681 kB | pg_default |
 shop      | postgres | UTF8     | libc            | C       | C     |        |           |                       | 7681 kB | pg_default |
 template0 | postgres | UTF8     | libc            | C       | C     |        |           | =c/postgres          +| 7681 kB | pg_default | unmodifiable empty database
           |          |          |                 |         |       |        |           | postgres=CTc/postgres |         |            |
 template1 | postgres | UTF8     | libc            | C       | C     |        |           | =c/postgres          +| 7839 kB | pg_default | default template for new databases
           |          |          |                 |         |       |        |           | postgres=CTc/postgres |         |            |
(5 rows)

The extra columns are on the right: Size, Tablespace and Description.

Step 4: List Only Matching Databases

postgres=#Plain text
\l s*
OutputPlain text
                                             List of databases
  Name  |  Owner   | Encoding | Locale Provider | Collate | Ctype | Locale | ICU Rules | Access privileges
--------+----------+----------+-----------------+---------+-------+--------+-----------+-------------------
 school | postgres | UTF8     | libc            | C       | C     |        |           |
 shop   | postgres | UTF8     | libc            | C       | C     |        |           |
(2 rows)

Step 5: List Database Names with SQL

When a script or an app needs the names, ask the pg_database system table:

postgres=#SQL
SELECT datname FROM pg_database WHERE NOT datistemplate ORDER BY datname;
OutputPlain text
 datname
----------
 postgres
 school
 shop
(3 rows)

WHERE NOT datistemplate leaves out the template databases, and ORDER BY datname sorts the names.

Step 6: List Databases Without Opening psql

In Terminal, -l makes psql print the list and quit:

TerminalBash
psql -U postgres -l
OutputPlain text
Password for user postgres:
                                                List of databases
   Name    |  Owner   | Encoding | Locale Provider | Collate | Ctype | Locale | ICU Rules |   Access privileges
-----------+----------+----------+-----------------+---------+-------+--------+-----------+-----------------------
 postgres  | postgres | UTF8     | libc            | C       | C     |        |           |
 school    | postgres | UTF8     | libc            | C       | C     |        |           |
 shop      | postgres | UTF8     | libc            | C       | C     |        |           |
 template0 | postgres | UTF8     | libc            | C       | C     |        |           | =c/postgres          +
           |          |          |                 |         |       |        |           | postgres=CTc/postgres
 template1 | postgres | UTF8     | libc            | C       | C     |        |           | =c/postgres          +
           |          |          |                 |         |       |        |           | postgres=CTc/postgres
(5 rows)

Clean Up

To remove the two example databases, run:

postgres=#SQL
DROP DATABASE school;
DROP DATABASE shop;
OutputPlain text
DROP DATABASE
DROP DATABASE

Warning: DROP DATABASE deletes the database and everything in it at once. There’s no undo, so check the name before you press Return.

Common Mistakes

MistakeWhat happensDo this instead
Typing MySQL’s SHOW DATABASES;ERROR: unrecognized configuration parameter "databases"Use \l, or the pg_database query.
Typing \L in capitalsinvalid command \L Try \? for help.psql’s commands are lowercase: \l.

FAQ

Does \l show databases I can’t connect to?

Yes. It lists every database on the server. Whether you can connect to one depends on your permissions.

How do I see how big each database is?

Use \l+. Its Size column shows each database’s size on disk.

What are template0 and template1?

Templates. When you create a database, PostgreSQL copies template1 by default; template0 stays untouched as a clean original.

Conclusion

Type \l in psql to list your databases, \l+ to add their sizes, and a pattern such as \l s* to narrow the list. Use the pg_database query when you need only the names.

Additional Resources

Postgres, PostgreSQL and the Slonik Logo are trademarks or registered trademarks of the PostgreSQL Community Association of Canada, and used with their permission.

0 comments