How to List All Databases in PostgreSQL

June 9, 2021

Introduction

One of the important tasks when managing PostgreSQL servers is listing the existing databases and their tables. There are three ways to list all databases:

  • Using meta-commands
  • Running a query against a server
  • Via the GUI tool pgAdmin.

This tutorial will show you how to list all databases in PostgreSQL and inspect which tables a database contains.

Learn how to list all databases in PostgreSQL.

Prerequisites:

  • PostgreSQL installed and set up
  • Administrator privileges

List Databases via psql Terminal

The psql terminal is a front end to PostgreSQL, allowing users to interact with the server by running queries, issuing them to PostgreSQL, and displaying the results.

psql allows users to use meta-commands, useful commands starting with a backslash \. Use these commands to perform routine tasks, such as to connect to a database, see all databases, etc.

Note: Read our tutorial and learn to create databases in PostgreSQL.

To list all the databases in the server via the psql terminal, follow these steps:

Step 1: Open the SQL Shell (psql) app.

Open the SQL Shell (psql) app.

Step 2: Press ENTER four times to connect to the DB server. Enter your password if asked. If you didn't set up a password, press ENTER again to connect.

Connect to the database server using psql terminal.

Step 3: Run the following command:

\l
Output showing a list of all databases in PostgreSQL

The output shows a list of all databases currently on the server, including the database name, the owner, encoding, collation, ctype, and access privileges.

Note: If you want to see additional information about size, tablespace, and database descriptions in the output, use \l+.

List Databases via SQL Query

Another method to list databases in PostgreSQL is to query database names from the pg_database catalog via the SELECT statement. Follow these steps:

Step 1: Log in to the server using the SQL Shell (psql) app.

Step 2: Run the following query:

SELECT datname FROM pg_database;
List all databases in psql using the SELECT statement.

psql runs the query against the server and displays a list of existing databases in the output.

Note: Learn the difference between PostgreSQL and MySQL in our comparison article.

List Databases via pgAdmin

The third method to see databases on the server is to use pgAdmin. pgAdmin is the leading open-source GUI tool for managing PostgreSQL databases.

Follow these steps to see all databases on the server using pgAdmin:

Step 1: Open the pgAdmin app and enter your password to connect to the database server.

Open pgAdmin and connect to the database server.

Step 2: Expand the Servers tree and then the Databases tree. The tree expands to show a list of all databases on the server. Click the Properties tab to see more information about each database.

Click the Properties tab to see more information about each database

List Tables

After listing all existing databases on the server, you can view the tables a database contains. You can achieve this by using psql or using pgAdmin.

See tables in psql

Step 1: While you're logged in, connect to the database you want to inspect. The syntax is:

\c [database_name]

For example:

An example of connecting to a database in psql

Step 2: List all database tables by running:

\dt
The output of table names and their schema, type, and owner

The output includes table names and their schema, type, and owner.

If there are no tables in a database, the output states that no relations were found.

Note: To see more information about tables, including their sizes and descriptions, run \dt+.

See tables in pgAdmin:

Step 1: After logging in to pgAdmin, expand the Servers tree, Databases tree, and click the database you want to inspect.

Step 2: In the expanded database tree, click Schemas, followed by Tables. The Properties tab shows a list of all tables, and they show up in the Tables tree as well.

See all tables of a database in pgAdmin.

Conclusion

The guide provided the instructions for listing all databases and their tables on your PostgreSQL server. Choose pgAdmin for a GUI approach or use psql if you prefer to administer your database via a terminal.

Was this article helpful?
YesNo
Bosko Marijan
Having worked as an educator and content writer, combined with his lifelong passion for all things high-tech, Bosko strives to simplify intricate concepts and make them user-friendly. That has led him to technical writing at PhoenixNAP, where he continues his mission of spreading knowledge.
Next you should read
How to Check Your PostgreSQL Version
April 21, 2020

Use this guide to check your PostgreSQL version with a few straightforward commands. You can retrieve the PostgresSQL version directly from your command line or use an effective SQL statement within the PostgreSQL shell.
Read more
How to Export a PostgreSQL Table to CSV
March 17, 2020

Learn how to export a PostgreSQL table to a .csv file. This feature is especially helpful when transferring the table to a different system or importing it to another database application.
Read more
How to Install SQL Workbench for PostgreSQL
March 13, 2020

Save time and effort by managing different database systems with a single tool. Find out how to set up SQL Workbench to connect to a PostgreSQL database with four (4) easy steps.
Read more
How to Deploy PostgreSQL on Docker Container
January 16, 2020

PostgreSQL is the third most popular Docker image used for deploying containers. Run PostgreSQL on Docker by pulling the Postgres image from Docker's official repository and set up the database service for your application.
Read more
  • © 2021 Copyright phoenixNAP | Global IT Services. All Rights Reserved.