How to List Databases in PostgreSQL

How do I list all databases in Amazon Redshift?

Lists every non-template database in your Redshift cluster so you can confirm names, owners, and encodings.

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

What is the fastest way to list databases in Redshift?

Run SHOW DATABASES; in any SQL client connected to your Redshift cluster. The command returns every database name, its owner, and ACLs in milliseconds.

How do I list databases with psql?

Inside the psql shell, type \l or \list. Redshift treats the backslash meta-command the same as PostgreSQL and prints the databases in a formatted table.

Can I filter out system and template databases?

Yes. Use a catalog query:

SELECT datname FROM pg_database WHERE datistemplate = false AND datname NOT IN ('rdsadmin');

What columns are returned?

SHOW DATABASES returns database_name, owner, and acl. The pg_database catalog supplies extra columns such as encoding, datcollate, and datctype.

When should I query the catalog instead of SHOW?

Query the catalog when you need filters, ordering, or additional metadata (size, encoding, creation date). SHOW DATABASES is best for a quick, unfiltered list.

Best practices for listing databases

Use SHOW DATABASES in scripts for portability; fall back to pg_database when you need advanced filtering. Always exclude template0, template1, and rdsadmin from user-facing lists.

Why How to List Databases in PostgreSQL is important

How to List Databases in PostgreSQL Example Usage


-- Return only ecommerce-related databases
SELECT datname AS database
FROM pg_database
WHERE datistemplate = false
  AND datname LIKE 'ecom_%'
ORDER BY datname;

How to List Databases in PostgreSQL Syntax


SHOW DATABASES;
\l [PATTERN]      -- inside psql
\list [PATTERN]   -- alias for \l
SELECT datname
FROM pg_database
WHERE datistemplate = false   -- exclude templates
  AND datname NOT IN ('rdsadmin') -- exclude Redshift internal DB
ORDER BY datname;

Common Mistakes

Using SELECT * FROM pg_database without a WHERE clause. This returns template and system databases, cluttering results. Fix by adding WHERE datistemplate = false.
Running \l in a non-psql tool. Backslash meta-commands work only inside psql. Use SHOW DATABASES or a SELECT query in GUI clients.