How to List Databases in PostgreSQL

Galaxy Glossary

How do I list all databases in PostgreSQL?

Display all databases in the current PostgreSQL cluster using psql meta-commands or SQL.

Sign up for the latest in SQL knowledge from the Galaxy Team!

Description

What’s the fastest way to list databases?

Open psql and type \l or \list. PostgreSQL immediately shows every database, its owner, encoding, collate/cctype, and access privileges.

How does the `\l+` variant help?

Add + for extra columns such as database size and tablespace. Use it when you need storage insight before allocating more space.

Can I filter the output?

Yes. Append a pattern: \l sales* lists only databases whose names start with sales. Wildcards follow standard LIKE semantics.

What if I’m not in `psql`?

Run a SQL query from any client: SELECT datname FROM pg_database WHERE datistemplate = false;. Skip template databases to avoid noise.

Why am I seeing template databases?

psql shows every cluster database by default. Use a SQL WHERE clause or pattern filter to hide template0 and template1.

How do I check sizes for all databases?

Combine \l+ with \pset format aligned for a readable size column, or query pg_database_size(datname) in SQL.

Best practice: use least-privileged accounts

Create a read-only role that can connect to the cluster but not modify data. Grant it CONNECT on required databases before listing.

Best practice: script with `--tuples-only`

Automate audits by invoking psql -Atc "\l". The -A flag removes alignment, and -t strips headers for easy parsing.

Best practice: monitor database count

Excessive databases can slow pg_dumpall. Schedule a nightly job that logs SELECT count(*) FROM pg_database WHERE datistemplate = false;.

Why How to List Databases in PostgreSQL is important

How to List Databases in PostgreSQL Example Usage


SELECT datname AS database,
       pg_size_pretty(pg_database_size(datname)) AS size
FROM   pg_database
WHERE  datistemplate = false
ORDER  BY size DESC;

How to List Databases in PostgreSQL Syntax


-- psql meta-command
\l[+] [pattern]

-- SQL alternative
SELECT datname
FROM pg_database
WHERE datistemplate = false
  [AND datname LIKE 'pattern'];

Common Mistakes

Querying pg_database without filtering template databases: <code>SELECT datname FROM pg_database;</code> returns template0 and template1. Fix by adding <code>WHERE datistemplate = false</code>.
Running <code>\l</code> in a GUI that doesn’t support psql meta-commands. Instead, open a psql session or use the SQL query on pg_database.

Frequently Asked Questions (FAQs)

How do I list databases from the shell without interactive psql?

Use psql -Atc "\\l" postgres. The flags produce unaligned, header-free output, ideal for scripts.

Can I see who owns each database?

Yes—owner appears in the \l output. In SQL, query datname, pg_catalog.pg_get_userbyid(datdba) from pg_database.

How can I show only databases larger than 1 GB?

Run SELECT datname, pg_size_pretty(pg_database_size(datname)) FROM pg_database WHERE datistemplate = false AND pg_database_size(datname) > 1073741824;.

Want to learn about other SQL terms?

dbt tests

dbt tests are declarative assertions in dbt that automatically validate data quality during model builds.

dbt utils

dbt utils is a community-maintained dbt package that adds time-saving utility macros for analytics engineers, such as generating surrogate keys, safely unioning models, and automating date spines.

dbt Tutorial Glossary: Build, Test & Deploy SQL Models

dbt is an open-source framework that lets data teams transform raw warehouse tables into tested, documented, production-ready datasets using version-controlled SQL.

Trusted by top engineers on high-velocity teams

Assort Health

Curri

Rubie