PostgreSQL provides a “\dt” command to list all the available tables of a database. Postgres offers another useful command named “\dt+” that provides some extra/detailed information about the tables, such as table size, access method, etc. In addition to this, Postgres facilitates its user with some built-in schemas, such as pg_catalog and information_schema that are used to list all the tables.

In this post, you will learn how to list all the available tables using SQL SHELL(psql) or pgAdmin. So, let’s start with the first method.

PostgreSQL List All Tables Using SQL SHELL(psql)

Here are stepwise instructions to list all the tables of a particular database using SQL SHELL:

Step #1: Log in to the SQL SHELL

Firstly, open the SQL SHELL and provide all the necessary details, such as port number, user name, and super user password, as shown in the following snippet:

Hit the “ENTER” button after specifying the appropriate password; consequently, the following interface will appear:

The above snippet indicates that we are successfully connected to the default database, i.e., “postgres”.

Step #2: Check Available Databases

Executing the below-mentioned command will show all the available databases:

\l;

The output shows the list of all the available databases.

Step #3: Establish a Connection With the Desired Database

Suppose we want to access the “example” database; for that purpose, we will run the following command:

\c example;

The output indicates that we are successfully connected to the “example” database.

Step #4: List All Tables

Once you are connected to the desired database, you can run the below command to list all the tables/relations of that particular database:

\dt;

Congratulations! You have successfully listed all the relations/tables of the desired database using SQL SHELL.

Step #5: List All Tables With Detailed Information

Execute the below command to get detailed information about each table, such as the table’s size, persistence, etc.

\dt+;

This way, you can list all the relations using the psql tool.

PostgreSQL List All Tables Using pg_catalog

Postgres provides a schema named pg_catalog that is used to list all the available tables of a database. Follow the below-listed steps to show/list all the tables using pg_catalog schema:

Step #1: Open pgAdmin

Let’s open the pgAdmin and provide the superuser password as shown below:

Clicking on the OK button will log you into the pgAdmin.

Step #2: Open Query Tool

Right-click on the selected database and select the Query tool:

Clicking on the “Query Tool” will open the query tool where you can execute any query of your choice.

Step #3: List Tables Using pg_catalog

Let’s run the below command to list all the tables using the pg_catalog schema:

SELECT *
FROM pg_catalog.pg_tables;

The output shows that the above query fetched/listed all the tables, including system tables.

Step #4: List User-Defined Tables Using pg_catalog

To retrieve only user-defined tables, execute the below query:

SELECT *
FROM pg_catalog.pg_tables
WHERE schemaname != 'pg_catalog' AND 
schemaname != 'information_schema';

The output shows that this time we get only user-defined tables.

PostgreSQL List All Tables Using information_schema

Let’s run the below-given command to get the list of all tables:

SELECT * FROM information_schema.tables;

This way, users can get the list of all relations using the information schema.

Conclusion

In Postgres, an SQL command: “\dt”, and built-in schemas: pg_catalog and information_schema, are used to list all the tables of a database. The “\dt” or “\dt+” commands will be executed from the SQL Shell psql, however, you can run the pg_catalog or information_schema from any interface of your choice, such as pgAdmin or psql. This write-up demonstrated different methods to list all the tables in PostgreSQL.