POSTGRESQL

How to List Databases in Postgres Using psql and Other Techniques

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.

Tools used in the tutorial
Tool Description Link
Dbvisualizer DBVISUALIZER
TOP RATED DATABASE MANAGEMENT TOOL AND SQL CLIENT
PostgreSQL logo POSTGRESQL
THE POSTGRESQL DATABASE

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:

  • With a command-line command
  • With a query
  • In a database client

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:

  • Create databases, tables, and users.
  • Run queries on a Postgres database.
  • Launch commands to get metadata about your databases.

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:

  1. Connect to the PostgreSQL database server with psql command-line tool:
Copy
        
$ 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:

  • Set the password in the PGPASSWORD environment variable
  • Add the password to the .pgpass file
  1. List the databases with:
Copy
        
$ \l

["This will return a table containing the databases of the PostgreSQL server in the format below:"]

The result of the \l command.
The result of the "\l" command

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:

The result of the \l+ command. Note that the table now involves more columns.
The result of the "\l+" command. Note that the table now involves more columns

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:

Copy
        
1 SELECT * FROM pg_catalog.pg_database
Executing the query in DbVisualizer.
Executing the query in DbVisualizer

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:

Copy
        
1 SELECT * FROM pg_catalog.pg_database 2 WHERE datistemplate = false;
Note that the template databases are no longer part of the resulting table
Note that the template databases are no longer part of the resulting table

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:

  1. Connect to your PostgreSQL server as explained in the official doc.
  2. Select your target server in the “Connections” menu on the left, right-click on it, and choose “Connect.”
Connecting to a new database in DbVisualizer
Connecting to a new database in DbVisualizer
  1. Open the “Databases” dropdown to see the list of databases.
The list of databases available in DbVisualizer
The list of databases available in DbVisualizer

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:

Copy
        
$ 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:

Copy
        
$ \c

Then, use the \dt command:

Copy
        
$ \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?

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.

Dbvis download link img
About the author
Antonello Zanini

Antonello is a software engineer, and often refers to himself as a technology bishop. His mission is to spread knowledge through writing.

The Table Icon
Sign up to receive The Table's roundup
More from the table
Title Author Tags Length Published
title

5 Ways to Split a String in PostgreSQL

author Antonello Zanini tags POSTGRESQL Split 6 min 2024-02-29
title

PostgreSQL CASE: A Comprehensive Guide

author Leslie S. Gyamfi tags CASE POSTGRESQL 7 min 2024-02-22
title

Discover All PostgreSQL Data Types

author Antonello Zanini tags DATA TYPES POSTGRESQL 12 min 2024-02-05
title

A Complete Guide to pg_dump With Examples, Tips, and Tricks

author Antonello Zanini tags pg_dump POSTGRESQL 8 min 2024-01-25
title

A Comprehensive Guide to Data Types in Postgres

author Leslie S. Gyamfi tags DATA TYPES POSTGRESQL 10 min 2024-01-23
title

SQL DISTINCT: A Comprehensive Guide

author Bonnie tags DISTINCT POSTGRESQL SQL 5 MINS 2024-01-11
title

Date Formatting in Postgres: A Comprehensive Guide

author Leslie S. Gyamfi tags DATE POSTGRESQL 7 MINS 2023-12-11
title

Casting in PostgreSQL: Handling Data Type Conversions Effectively

author Leslie S. Gyamfi tags CAST POSTGRESQL 5 MINS 2023-12-07
title

PostgreSQL Materialized Views: A Beginner's Guide

author Ochuko Onojakpor tags POSTGRESQL VIEWS 7 MINS 2023-12-04
title

Postgres List Users: Two Different Approaches 

author Antonello Zanini tags LIST USERS POSTGRESQL 3 MINS 2023-11-16

The content provided on dbvis.com/thetable, including but not limited to code and examples, is intended for educational and informational purposes only. We do not make any warranties or representations of any kind. Read more here.

Cookie policy

We use cookies to ensure that we give you the best experience on our website. However you can change your cookie settings at any time in your browser settings. Please find our cookie policy here ↗