PostgreSQL - Show Database

PostgreSQL - Listing Databases

In PostgreSQL, unlike some other database systems like MySQL which have a direct SHOW DATABASES command, listing databases is typically done by querying the system catalog or using a client-specific meta-command. PostgreSQL maintains a wealth of information about its state and objects in a set of system tables, collectively known as the system catalog.

There are two primary methods to list all available databases:

• Using a SQL SELECT query on the pg_database catalog.
• Using meta-commands in the psql command-line client.

Method 1: Using a SQL SELECT Query

The most universal method, which works in any SQL client, is to query the pg_database system catalog. This table contains a row for each database in the cluster.

SELECT datname FROM pg_database;

This basic query returns only the names of the databases. Here is a sample output:

  datname
-----------
 postgres
 template1
 template0
 my_app_db
(4 rows)

Enhanced Query for More Details

To get more useful information, you can select additional columns. For example, you can filter out template databases which are not typically used for direct application work.

SELECT
  datname AS "Database Name",
  pg_catalog.pg_get_userbyid(datdba) AS "Owner",
  pg_encoding_to_char(encoding) AS "Encoding",
  datcollate AS "Collate",
  datctype AS "Ctype",
  pg_catalog.pg_size_pretty(pg_catalog.pg_database_size(datname)) AS "Size"
FROM pg_catalog.pg_database
WHERE datistemplate = false;

This query provides a much richer, human-readable overview of your non-template databases.

Method 2: Using psql Meta-Commands

psql is the official interactive terminal for PostgreSQL. It provides convenient meta-commands (prefixed with a backslash \) for administrative tasks. These commands are shortcuts for longer SQL queries.

Syntax

To list databases within a psql session, you can use:

\l

Or, for a more detailed view including database size and description:

\l+

Example

First, connect to your PostgreSQL server using psql. You will be prompted for your password.

psql -U postgres

Once connected, run the \l command:

postgres=# \l
                                 List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    | Access privileges
-----------+----------+----------+-------------+-------------+-------------------
 my_app_db | my_user  | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres ...
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres ...
(4 rows)

Best Practices & Common Errors

Which Method to Use?

• Use psql meta-commands (\l, \l+) for quick, interactive checks during development or administration directly on the server.
• Use the SELECT query for programmatic access, such as within an application, script, or a database GUI that is not psql.

Common Pitfall

A frequent mistake for beginners is trying to run psql meta-commands like \l in a general-purpose SQL client or from an application’s database driver. These commands are exclusive to the psql tool and will result in a syntax error elsewhere. Always use the SELECT FROM pg_database query in non-psql environments.