Use Psql to List Tables in PostgreSQL

Introduction

When you’re using PostgreSQL to store data in tables, there may be times when you need to obtain a list of all the tables in a given database. In some cases, you may only want a list of the tables you created, omitting certain system tables created by PostgreSQL. Fortunately, it’s easy to access the exact table information you need using the psql command-line interface for PostgreSQL. In this article, we’ll show you how to connect to your database and use psql to list tables in PostgreSQL.

Prerequisites

Before we go any further with our tutorial, let’s look at a few prerequisites that are essential for this task:

• You’ll need to have a PostgreSQL database cluster installed on your local device. If you’re not sure whether it’s installed, you can verify by using systemd in Linux. Just type the command sudo systemctl status postgresql to see if PostgreSQL is actively running on your machine.

• To find out which version of PostgreSQL is installed, just use the command psql -v in the terminal.

Access psql

In order to execute queries and other commands in the PostgreSQL database, you’ll need to access the psql console. It’s in this command-line interface where you’ll be able to input SQL commands and list the tables in a certain database.

To enter and access the psql console, use the following command:

After entering the password, type the command psql to access the database.

PostgreSQL list tables

Once you’re in the psql console, all you need to do to list tables in a given database is to use the command \dt inside the current database. Of course, it’s important to connect to that database first.

Connect to a PostgreSQL database

Using the psql console, enter the command \l to display a list of all the database in your PostgreSQL environment. You should see output that looks like this:

Next, enter the command \c to connect to a specific database for which you’d like to list the tables.

The basic syntax used to list tables in PostgreSQL database is:

When you execute this command, your results will look like the following:

Using the pg_catalog schema

Another way is to list tables in PostgreSQL is by using the SELECT statement to query the data of the pg_catalog schema:

Let’s look at an example of this query in action:

NOTE: Notice that we omit tables that come from pg_catalog and information_schema; this ensures that your query only returns tables that you have created.

Using this query, we’ll get results that look like the following:

As you can see, the query was successful, returning the demo and employee tables that exist within the current database.

Conclusion

Being able to list tables in PostgreSQL is an important component of good database administration. Whether you want to list all tables in a given database or just the ones that you created, it’s easy to get the appropriate information using the psql command-line interface. In this article, we looked at a few different ways you can use psql to list tables in PostgreSQL. Using the methods described in this tutorial, you’ll have no problem obtaining a list of tables in your own PostgreSQL environment.