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
\l[+] [ pattern ]
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, sos*means names that start with s.pg_database: a system table with one row per database. Itsdatnamecolumn 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:
CREATE DATABASE school;
CREATE DATABASE shop;
CREATE DATABASE
CREATE DATABASE
Step 2: List All Databases with \l
\l
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+
\l+
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
\l s*
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:
SELECT datname FROM pg_database WHERE NOT datistemplate ORDER BY datname;
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:
psql -U postgres -l
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:
DROP DATABASE school;
DROP DATABASE shop;
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
| Mistake | What happens | Do this instead |
|---|---|---|
Typing MySQL’s SHOW DATABASES; | ERROR: unrecognized configuration parameter "databases" | Use \l, or the pg_database query. |
Typing \L in capitals | invalid 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
- PostgreSQL 19 documentation: psql (opens in a new tab)
- PostgreSQL 19 documentation: pg_database (opens in a new tab)
- PostgreSQL 19 documentation: Managing Databases (opens in a new tab)
Related
- PostgreSQL Tutorial — Part 2: Connect with psql and Run Your First Commands
- How to Switch Databases in psql
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