intro
A single PostgreSQL server can contain many databases, and there are several ways to list them all. Explore three different approaches to get the list of databases in a PostgreSQL server.
When working with PostgreSQL, developers, and DBAs are certainly interested in listing the databases available on your server. This is one of the most common operations performed by developers working on PostgreSQL. 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 showing PostgreSQL databases:
Let’s dig into PostgreSQL database listing!
Listing Databases With the psql Command-Line Tool
psql allows 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 how to use psql to list databases in the terminal:
$
psql -U username
Replace username with the username of the PostgreSQL user you want to log in with. psql will ask you to enter the password associated with the user. Type it and press Enter to connect to the database.
Keep in mind that users can observe 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:
$
\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:
Listing PostgreSQL Databases With a Query
If the command line is not your thing, keep in mind that you can also list databases in Postgres by querying pg_catalog.pg_database. Specifically, 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 * FROM pg_catalog.pg_database
The datname column stores the name of each database. Note that this table also includes the database templates used by PostgreSQL to initialize a new database when a CREATE DATABASE query is launched. Filter them out with the query below:
1
SELECT * FROM pg_catalog.pg_database
2
WHERE datistemplate = false;
View Databases in a Postgres Client
The last and easiest way to show all databases in PostgreSQL is through a database client, such as DbVisualizer. This powerful tool allows you to visually connect and manage your Postgres databases.
In detail, DbVisualizer is a PostgreSQL client with extended support for pgSQL specific object types and features. All you have to do to view the databases available in a PostgreSQL server in DbVisualizer is:
Fantastic! DbVisualizer allows you to view all PostgreSQL databases with just a couple of clicks.
Conclusion
In this article, you understood that listing databases in PostgreSQL is a simple task, and there are at least three methods to achieve that. The first involves a command-line command, the second a query, and the third a GUI tool. In particular, 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 PostgreSQL databases with a single command?
You can get the list of databases available in a Postgres server with a single command with:
$
psql -U -l
Replace with an actual username. This command will connect to the PostgreSQL server and then directly launch the \l command to get the list of 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:
$
\c
Then, use the \dt command:
$
\dt
This will return all the tables in the selected database.
What is the easiest way to list databases in PostgreSQL?
The easiest way to show databases in PostgreSQL is through a database client. This is because a PostgreSQL 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 PostgreSQL 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.
Are there any security concerns when listing databases in PostgreSQL?
