Learn 2 easy ways to PostgreSQL list databases

There are several meta commands in PostgreSQL that can be used to list databases. These commands are helpful for list databases and also retrieving other database details.

List Databases in PostgreSQL by psql command

A single PostgreSQL database can have multiple databases. To list all the databases in PostgreSQL using command line, we can use \l or \list command. In addition to database name, this command also provides owner, encoding and other attributes.

If you need database size, tablespace, and description of the database, then use \l+ or \list+ command like shown below.

List database using pg_database table

We can also use pg_database table to list all databases in PostgerSQL server.

select datname from pg_database;

Search database name by regular expression

We can also use regular expression to search database name using pattern. Below is the list of syntax.

\l [pattern]

Regular expression operators like * and ? can be used in the pattern to search databases by name. Here’s an example of it.

\l my*

Summary

• The command \l or \l+ is utilized to display a list of databases on a PostgreSQL server.
• We can use the pg_database table to retrieve all database names.
• Use regular expression after \l to search database name based on patterns.