Site icon JD Bots

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)

\l[+] [ pattern ]

Syntax (SQL)

SELECT datname FROM pg_database;

\l is described in the psql documentation, and pg_database in its catalog page.

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=#

CREATE DATABASE school;
CREATE DATABASE shop;

Output

CREATE DATABASE
CREATE DATABASE

Step 2: List All Databases with \l

postgres=#

\l

Output

                                                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=#

\l+

Output

                                                                                  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=#

\l s*

Output

                                             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=#

SELECT datname FROM pg_database WHERE NOT datistemplate ORDER BY datname;

Output

 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:

Terminal

psql -U postgres -l

Output

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=#

DROP DATABASE school;
DROP DATABASE shop;

Output

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

Related

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

Exit mobile version