Listing tables in PostgreSQL is essential for organizing, managing, and utilizing the table’s data. For this purpose, Postgres provides different built-in commands and queries. For instance, you can gain instant access to a comprehensive list of all the tables with the “\dt” command, “information_schema”, “pg_tables”, etc. Using these commands and queries, you can get the list of tables from a specific database or list of all available tables in Postgres.

This blog will demonstrate how to get the list of all available tables in Postgres using practical examples.

How to List All Available Tables/Relations in Postgres?

Let’s learn how to list all the available tables in Postgres using the following methods:

• Method 1: Using “\dt” Command
• Method 2: Using “\d” Command
• Method 3: Using information_schema
• Method 4: Using “pg_tables”

Method 1: Using “\dt” Command

To list tables from all schemas, use the “\dt” command as follows:

\dt *.*;

This command retrieves all the tables(including system and user-defined tables) from all the schemas. To get the list of tables from a specific schema, use the “\dt” command as follows:

\dt public.*;

This time the “\dt” command will retrieve the tables from the public schema only:

Similarly, to list all the available tables of a specific Postgres database, you can use the command “\dt”. But for this purpose, firstly, you need to establish a connection with that particular database.

For instance, to list the available tables of the “example” database, firstly, we will connect to the example database using the following “\c” command:

\c example;

Now execute the following “\dt” command to list down all the available tables of the example database:

\dt;

The “\dt” command retrieves the list of all the tables/relations available in the selected database, i.e., “example”.

Method 2: Using “\d” Command

The “\d” command can also be used to list all the available relations of a specific database. However, it will retrieve not only the tables but also the views, indexes, sequences, etc.

\d;

The output shows that the “\d” command retrieves the list of available tables, views, sequences, etc. Using the “\d” command, you can describe all the tables available within a specific schema, as shown in the following snippet:

\d public.*;

The output demonstrates that the “\d” command describes all the tables of the “public” schema.

Method 3: Using information_schema

In Postgres, the “information_schema” can be used to list all the available tables:

SELECT table_name
FROM information_schema.tables;

The output shows that the information_schema retrieves all available tables. To get all the tables of some specific schema, you need to specify the name of that particular schema:

SELECT table_name
FROM information_schema.tables
WHERE table_schema = 'public';

This time the “information_schema” retrieves all the tables of the public schema.

Method 4: Using “pg_tables”

An alternate way of listing all tables is by querying the system catalog table called pg_tables. For this purpose, the below-provided query will be used in Postgres:

SELECT tablename, tableowner
FROM pg_tables 
WHERE schemaname = 'public';

The above query retrieves all the tables of the “public” schema. Users can replace the “public” schema with the desired one to get the list of tables from that particular schema.

You can also use the “pg_catalog.pg_tables” to get the list of all the available tables from a specific database:

SELECT tablename, tableowner
FROM pg_catalog.pg_tables 
WHERE schemaname != 'pg_catalog' AND 
schemaname != 'information_schema';

Here, the WHERE clause is used to filter the user-defined tables only. If we omit the WHERE clause, then the “pg_catalo.pg_tables” will retrieve all tables, including system tables and user-defined tables:

This is how you can get the list of all the tables from a specific database.

Conclusion

PostgreSQL provides different built-in commands and queries to get the list of all the tables, such as the “\dt” command, “pg_tables”, “information_schema”, etc. These commands and queries allow us to get the list of tables from a specific database, schema, or a list of all available tables in Postgres. Using examples, this blog presents a detailed overview of how to list tables in PostgreSQL.