intro
A single PostgreSQL server can contain many databases. Let’s explore three different approaches to list databases in PostgreSQL!
Getting the list of databases in Postgres is one of the most common operations performed by developers and DBAs. There are several ways to achieve that, depending on preferences, requirements, and experience.
In this article, you will find out more about three different approaches to getting the list of databases in PostgreSQL:
Let’s dig into how to list all Postgres databases!
Approach #1: List Databases With the psql CLI
psql enables you to interact with a PostgreSQL server via the command line. “psql” stands for "PostgreSQL interactive terminal" and allows you to:
In detail, psql provides the l command to get the list of databases in a PostgreSQL server.
Follow the steps below to learn the Postgres psql list databases approach!
Step #1: Connect to the PostgreSQL database server with psql command-line tool:
1
psql -U <username>
Replace <username> with the username of the PostgreSQL user you want to log in with. Keep in mind that the default Postgres username is postgres.
psql will ask you to enter the password associated with the user. Type it and press Enter to connect to the database.
If you are a Windows user, you may get the following error:
1
psql : The term 'psql' is not recognized as the name of a cmdlet, function, script file, or operable program. Check
2
the spelling of the name, or if a path was included, verify that the path is correct and try again.
If that is your case, refer to our dedicated FAQ question.
Keep in mind that users can see executed commands by observing the history of commands in the CLI. This will allow them to see the password you typed. If you want to prevent entering the password for security reasons, you can:
Now that you are logged in, you can get the list databases in Postgres with this command:
1
\l
This will return a table containing the databases of the PostgreSQL server in the format below:
Note that l is the shortened version of the list command. So, list will return the same result:
Use l+ or list+ to get additional information about each database, such as the size in Kb and its description:
Approach #2: List Databases in Postgres With a Query
If the command line is not your thing, remember that you can also list databases in PostgreSQL by querying pg_catalog.pg_database. That Postgres catalog contains a row for each database in the server.
Retrieve the list of databases in PostgreSQL with the query below:
1
SELECT *
2
FROM pg_catalog.pg_database;
The result will be:
The datname column stores the name of each database. In particular, below is a summary table with the columns contained in the pg_database catalog:
| Column | Type | Description |
|---|---|---|
oid | oid | Row identifier |
datname | name | Database name |
datdba | oid (references pg_authid.oid) | Database owner, usually the user who created it |
encoding | int4 | Character encoding for the database |
datlocprovider | char | Locale provider: b = builtin, c = libc, i = ICU. |
datistemplate | bool | If true, any user with CREATEDB privileges can clone the database; otherwise, only superusers or the owner can |
datallowconn | bool | If false, no one can connect to the database. Used to protect template0 from modification |
dathasloginevt | bool | Indicates if login event triggers exist for this database. Internally used by PostgreSQL |
datconnlimit | int4 | Max concurrent connections allowed: -1 = no limit, -2 = database is invalid |
datfrozenxid | xid | Earliest transaction ID considered "frozen." Used for vacuuming to prevent transaction ID wraparound |
datminmxid | xid | Earliest multixact ID considered "frozen." Used for vacuuming to prevent multixact ID wraparound |
dattablespace | oid (references pg_tablespace.oid) | Default tablespace for the database. Tables with pg_class.reltablespace = 0 are stored here |
datcollate | text | LC_COLLATE setting for the database |
datctype | text | LC_CTYPE setting for the database |
datlocale | text | Locale name for collation provider. If libc, this is NULL and datcollate/datctype are used |
daticurules | text | ICU collation rules for the database. |
datcollversion | text | Version of the collation provider. Stored at creation and checked for changes that may affect data consistency |
datacl | aclitem[] | Access privileges. See PostgreSQL documentation (Section 5.8) for details |
If you are not familiar with some of the types above, read our guide on data types in PostgreSQL.
Notice that the returned dataset also includes the database templates used by PostgreSQL to initialize a new database when a CREATE DATABASE query is launched. You can filter them out with the query below:
1
SELECT *
2
FROM pg_catalog.pg_database
3
WHERE datistemplate = false;
This time, the result will be:
Great! The resulting table no longer includes the templates.
To list databases— without the templates— with their sizes in Kb, run:
1
SELECT datname, pg_size_pretty(pg_database_size(datname))
2
FROM pg_database
3
WHERE datistemplate = false;
The function pg_size_pretty(pg_database_size(datname)) returns the size of a PostgreSQL database in a human-readable format.
The result will be:
Awesome! You now know also the Postgres list databases pro tricks.
Approach #3: List Databases in a Visual PostgreSQL Client
The last and easiest way to show all databases in PostgreSQL is through a database client, such as DbVisualizer. This powerful, feature-rich tool gives you what you need to visually connect and manage your Postgres databases.
In detail, DbVisualizer is a PostgreSQL client with extended support for Postgres-specific object types and features. All you have to do to list databases in Postgres using DbVisualizer is:
Step #1: Create a database connection to your PostgreSQL server as explained in the official doc.
Step #2: Select your target server in the “Connections” menu on the left, right-click on it, and choose the “Connect” option:
Step #3: Open the “Databases” dropdown to see the list of databases, or right-click on it and open it in a new tab:
See how this is much easier and clearer compared to using the command line or a query. Even non-technical users could do it.
Fantastic! DbVisualizer allows you to list databases in PostgreSQL with just a couple of clicks.
Conclusion
In this article, you explored different ways to list databases in Postgres. No matter which method you choose, the task remains straightforward. The three main approaches include using a command-line command, executing a SQL query, or leveraging a GUI tool.
As shown here, a database client with full support for PostgreSQL such as DbVisualizer allows you to visually deal with databases and provide several advanced features, such as query optimization. Download DbVisualizer for free!
FAQ
How to list databases in Postgres?
You can list databases in PostgreSQL using the following approaches:
How to deal with “The term 'psql' is not recognized” error on Windows?
On Windows, psql is not usually installed globally. To use it, you must first navigate to the PostgreSQL installation folder. The bin directory contains psql.exe, so open a terminal and run the following command to navigate to it:
1
cd "C:\Program Files\PostgreSQL\17\bin"
Replace 17 with the version of PostgreSQL installed on your system. If you installed PostgreSQL in a different directory, adjust the path accordingly.
Once in the bin folder, you can launch psql by running:
1
.\psql.exe
How to list PostgreSQL databases with a single command?
You can get the list databases in Postgres with this single command:
1
psql -U <username> -l
Replace <username> with an actual username. This Postgres psql list databases command will connect to the server and then directly launch the l command to get the list of all databases.
How to get the list of tables in a database with psql?
If you want to get the list of tables contained in a specific database, first connect to a database in psql with:
1
\c <database_name>
Then, use the dt command:
1
\dt
This will return all the tables in the selected database. For more information, follow our Show Tables PostgreSQL Guide.
What is the easiest way to list databases in PostgreSQL?
The easiest way to list databases in PostgreSQL is through a database client. This is because a Postgres client gives you the ability to see the databases available on the server in a user-friendly interface. Such an approach does not involve queries or commands in the terminal and makes it a perfect solution for both experienced and non-experienced users.
How to use pgAdmin to view the list of databases in PostgreSQL?
To use pgAdmin to view a list of databases in PostgreSQL, open pgAdmin and connect to your Postgres server. Then, expand the “Servers” group in the left panel to see a list of servers. Click on the server that you want to list the databases for, and then expand the Databases group to see a list of databases.
What are the permissions required to list databases in Postgres?
To list databases in PostgreSQL, a user needs one of the following permissions:
Are there any security concerns when listing databases in PostgreSQL?
If the permissions on the PostgreSQL server have been not set correctly, a user can see databases they should not have access to. To mitigate this risk, it is important to assign the right groups and limit the use of superuser accounts to only when necessary.
What are database templates in PostgreSQL?
In PostgreSQL, database templates are predefined databases used as a base when creating new databases. The default templates are template0 and template1. template1 is the default for new databases and can be customized, while template0 remains unchanged for creating clean databases. Users can also create custom templates by modifying template1 or duplicating an existing database. These templates help standardize database configurations and speed up database creation.
