PostgreSQL – Show Tables

PostgreSQL does not support the SHOW TABLES statement directly like MySQL does but provides users with an alternative. In this article, we will explore them in detail.

Using Psql Shell:

One way to list all the tables in a database is to use the below command after logging into the database:

Syntax: \dt

Example:

In this example, we will query for the list of all tables in the sample database, ie, dvdrental.

First, log in to the sample database using the below command:

\c dvdrental

Now use the below command to list the tables of the same:

\dt

Output:

Using pg_catalog schema:

Another way to show tables in PostgreSQL is to use the SELECT statement to query data from the PostgreSQL catalog as follows:

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

Example:

In this example, we will query for the list of all tables in the sample database, ie, dvdrental.

First, log in to the sample database using the below command:

\c dvdrental

Now runt the below command to list the tables in the database:

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

Output: