In PostgreSQL, SQL SHELL (psql) and pg_catalog schema are used to show the tables. Execute the “\dt” command from the psql tool or use the pg_catalog schema with the aid of the SELECT query from the pgAdmin to show tables of the selected database.

Let’s learn how to show tables in PostgreSQL with the help of examples.

How to Show Tables in PostgreSQL Using SQL SHELL(psql)?

To show tables using psql, the “\dt” command is used. This section will present stepwise instructions to show the Postgres tables using psql:

Step 1: Open psql

Let’s open the psql tool and fill in the necessary details like user name, password, etc., to connect to the PostgreSQL:

Step 2: Access the Database Using \c Command

Once you are logged in, specify the database name after the “\c” command to access the desired database:

\c example;

The command mentioned above will connect us to the “example” database:

Congratulations! You are connected to the desired database.

Step 3: Show the List of Tables Using “\dt” Command

To see the available tables/relations within the example database, execute the “\dt” command as follows:

\dt;

The output shows that there are total “18” tables in the “example” database.

Step 4: Show the Tables Details Using “\dt+” Command

To show the tables with more details like table’s size, access method, description, etc., execute the “\dt+” command as follows:

\dt+;

This is how you can show the tables using the SQL SHELL(psql).

How to Show Tables in PostgreSQL Using pgAdmin?

In PostgreSQL, you can use the pg_catalog schema with the collaboration of the SELECT query to show the tables. By default, the pg_catalog will show all the relations, including systems tables; however, you can use the WHERE clause to filter the tables of your choice.

Step 1: Open the Query Tool

Firstly, open the pgAdmin and then right-click on the selected database and select the “Query Tool” as follows:

Clicking on the query tool will open the query editor as shown in the below screenshot:

Step 2: Show Tables Using pg_catalog

Let’s execute the query given below to show all the tables, including system relations:

SELECT *
FROM pg_catalog.pg_tables;

The output shows that the pg_catalog fetched all the relations, including system tables.

Step 3: Show Only User-defined Tables Using pg_catalog

Execute the query given below to show all the tables present in the example database:

SELECT *
FROM pg_catalog.pg_tables
WHERE schemaname NOT IN ('pg_catalog','information_schema');

In the WHERE clause, we specified a condition to skip the system’s tables. The query mentioned above will show all the tables except the system tables:

The pg_catalog schema succeeded in showing all the tables present in the “example” database. This is how you can show the tables of a database using pg_catalog schema.

Conclusion

Execute the “\dt” command from the psql tool or use the pg_catalog schema with the aid of the SELECT query from the pgAdmin to show tables of the selected database. This write-up demonstrated the working of the “\dt” command, “\dt+” command, and pg_catalog schema with the help of different examples.