How to list all available tables in PostgreSQL?

Checking the list of database tables is a common task that might be performed multiple times daily, and major database management systems provide built-in methods for this purpose.

PostgreSQL offers several methods that we will illustrate using the SQL Shell (psql) command-line tool and dbForge Studio for PostgreSQL, an advanced PostgreSQL IDE that covers all aspects of the database management.

Different ways to list tables in PostgreSQL

Continue reading to learn about:

How to connect to a PostgreSQL database using psql
How to list all tables using psql
How to obtain detailed information about tables
How to list tables using the pg_catalog schema
How to view and manage tables in PostgreSQL GUI tools

How to connect to a PostgreSQL database using psql

To begin, ensure you have a PostgreSQL database server installed on your computer along with the SQL Shell (psql) command-line utility (it comes by default with any PostgreSQL installation).

Launch this tool and connect to your PostgreSQL server with your credentials: server, host, username, and password.

To list all available databases, execute the \l command in psql:

\l

To switch to the necessary database, run the \c command:

\c database_name

For example:

\c Olympics

How to list all tables with psql

The command that retrieves the list of all tables available in a specific database is \dt:

\dt

The \dt command brings the list of tables from the public schema by default.

However, if you need to view all tables in all the database schemas, run the modified command as below:

\dt *.*

How to list PostgreSQL tables in a database

How to list all tables with detailed information

When you need more detailed information on all tables, execute the \dt+ command:

\dt+

You can also dive deeper and view more specific and detailed information about some particular table. For that, specify the table name in the command:

\d table_name

For example:

\d sports

How to list tables using the pg_catalog schema

Another way to fetch the list of tables in the PostgreSQL database is using the SQL SELECT query to address the PG_CATALOG schema.

In psql, you need first to connect to the necessary database using the standard \c command:

\c Olympics

Then, execute the below query to fetch the information from the PG_CATALOG schema:

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

How to list PostgreSQL tables using an SQL query

Display all tables in the PostgreSQL database with dbForge Studio

dbForge Studio for PostgreSQL enables users to retrieve the list of tables in two options.

First, you can execute queries directly against the desired database. Note that the Studio automatically formats the code for better readability, and you can customize code formatting for your needs. Query the PG_CATALOG schema:

SELECT * 
FROM pg_catalog.pg_tables;

Also, you can access tables via Database Explorer:

Select the desired database from the list on the left and expand the database node
Expand the relevant schema(s) to view the list of tables within that schema

How to view PostgreSQL database tables in dbForge Studio

Conclusion

In this article, we demonstrated different methods of listing tables in PostgreSQL databases using the standard psql command-line client and the GUI-based dbForge Studio for PostgreSQL. The Studio is one of the most functional and user-friendly GUI solutions for PostgreSQL on the market, with a robust feature set for all database-related tasks. You are welcome to try the full capacities of dbForge Studio for PostgreSQL with a fully functional free 30-day trial.