Postgres List Databases: psql and SQL Commands

To list databases in PostgreSQL, connect with the psql command-line client and run \l or \list. You can also query the pg_database system catalog with SQL; the choice depends on whether you want a quick terminal view or results you can filter and reuse.

List databases in psql

At the psql prompt, enter:

\l

The equivalent long form is:

\list

These are psql meta-commands, not SQL statements. They show databases the connected PostgreSQL server knows about, along with information such as the owner, encoding, locale settings, and access privileges. The exact columns can vary with PostgreSQL and client versions, and the output can be wider than a terminal window.

To include additional details, use the + form:

\l+

This can include database size and comments. It is useful when you need more than names, though displaying extra information may require additional catalog lookups.

Connect first, or list from the shell

If you are already at the psql prompt, run \l directly. If you are not connected yet, start psql with connection details, for example:

psql -U app_user -h localhost -p 5432 -d postgres

Here, -U sets the PostgreSQL role, -h the server host, -p the port, and -d the database to connect to. The example connects to a database named postgres; substitute values appropriate to your installation. The postgres database is commonly available for administrative connections, but it is not the only possible choice.

You can also run a psql meta-command from your operating-system shell without entering an interactive session:

psql -U app_user -h localhost -d postgres -c '\l'

This is convenient for scripts and quick checks. Shell quoting rules differ between operating systems and shells, so adjust the quotes if your shell reports a parsing error. The command still needs valid connection details and permission to connect.

List databases with SQL

For SQL tools, scripts, or filtered output, query PostgreSQL’s pg_database catalog:

SELECT datname
FROM pg_database
ORDER BY datname;

The catalog contains one row for each database in the PostgreSQL cluster. To show the database owner and whether connections are allowed, you can use:

SELECT datname,
       pg_get_userbyid(datdba) AS owner,
       datallowconn AS allows_connections
FROM pg_database
ORDER BY datname;

To display database sizes as well, use PostgreSQL’s pg_database_size function:

SELECT datname,
       pg_size_pretty(pg_database_size(datname)) AS size
FROM pg_database
ORDER BY datname;

These queries work in PostgreSQL-aware query tools as well as in psql. Unlike \l, SQL results can be sorted, filtered, joined, or saved by the client application. Catalog columns and available functions are documented by PostgreSQL; if you are writing software intended for multiple server versions, check the documentation for the versions you support.

What the list includes—and who can see it

A PostgreSQL cluster is a server instance managed as a unit, and it can contain multiple databases. A database is not the same thing as a schema: schemas are namespaces inside a database. You connect to one database at a time for ordinary SQL work, but the database list describes the cluster you connected to.

PostgreSQL commonly creates databases named postgres, template0, and template1. Their presence and use are different from application databases. Template databases are used when creating databases, and administrators generally should not treat them as ordinary application destinations. The exact set can vary because administrators can create or remove databases.

Database visibility is not the same as permission to connect or read data. A role may be able to see database names but lack the CONNECT privilege on a particular database. Conversely, seeing a database in the list does not grant access to its tables. PostgreSQL also limits visibility of some catalog details according to the connected role. For a complete administrative inventory, use an appropriately authorized role and follow the access policy for that server.

Choosing between psql and SQL

• Use \l for a fast human-readable inventory while working interactively in psql.
• Use \l+ when you want extra details such as size or comments in the client output.
• Use a SQL query when you need specific columns, sorting, filtering, or output from a script or database tool.

One common misunderstanding is trying to run \l in a generic SQL editor. It will fail because the backslash command belongs to the psql client; it is not sent to the server as SQL. In a SQL-only tool, use the pg_database query instead. Similarly, SHOW DATABASES is not PostgreSQL syntax; it is associated with other database systems.

Common connection and output problems

If psql says it cannot connect, the database list command has not yet run. Check that the server is running and verify the host, port, role, and connection database. A connection to localhost reaches the local machine; it will not reach a remote server unless that is where PostgreSQL is running or a tunnel is configured.

If you see fewer databases than expected, confirm that you are connected to the intended server or cluster. Different PostgreSQL instances can run on the same computer, sometimes on different ports. Also check the role and its permissions. A client may display a wide table awkwardly; in psql, use expanded display with \x when it helps, or select only the columns you need with SQL.

If a database appears in the results but connection attempts are rejected, check its connection privileges and whether connections are allowed for that database. Listing names alone does not diagnose authentication, network, or authorization issues.

Frequently Asked Questions

What is the command to list databases in PostgreSQL?
In psql, run \l or its equivalent, \list. Add a plus sign as \l+ for additional details.

How do I list PostgreSQL databases using SQL?
Run SELECT datname FROM pg_database ORDER BY datname;. The pg_database catalog stores information about databases in the connected cluster.

Can I use SHOW DATABASES in Postgres?
No. SHOW DATABASES is not PostgreSQL syntax. Use \l in the psql client or query pg_database with SQL.

Why does \l fail in my SQL editor?
\l is a psql meta-command, so a general SQL editor may send it to the server as though it were SQL. Use a query against pg_database instead.

Does listing a database mean I can connect to it?
No. Visibility of a database name does not grant the CONNECT privilege or access to the database’s contents. Those permissions are managed separately.

How do I see database sizes?
In psql, try \l+. In SQL, use pg_database_size(datname) and optionally pg_size_pretty to format the result for readability.

Related

• Wo Long 2 Wings of Ember Changes in Team Ninja’s Sequel
• Smart Toys for Kids: What They Are & How to Choose
• Recover Data from Mac That Won’t Boot