How to show all tables in PostgreSQL?

Introduction

PostgreSQL is a powerful, open-source database system. It is a relational database management system (RDBMS) based on the SQL language. PostgreSQL is one of the most popular database systems in the world. It is the go-to database for many developers and companies.

In this tutorial, we will show you how to list all tables in PostgreSQL using the psql command-line tool.

Prerequisites

To follow along with this tutorial, you will need:

• A PostgreSQL database server installed on your computer. You can download and install PostgreSQL from the official website. Or you can use a cloud-based PostgreSQL database such as DigitalOcean's managed PostgreSQL.
• psql command-line tool installed on your computer.

Step 1 — Connect to the PostgreSQL database

To connect to the PostgreSQL database, run the following command:

psql -U postgres -h localhost -p 5432

• The -U flag specifies the username to connect to the database. In this case, we are connecting to the default postgres user.
• The -h flag specifies the hostname of the database server. In this case, we are connecting to the database server running on the same computer, so we use localhost.
• The -p flag specifies the port number of the database server. In this case, we are connecting to the default port number 5432.

Step 2 — Switch to a specific database

To check the list of all databases, run the following command:

\l

That is the equivalent of running SHOW DATABASES; in MySQL.

To switch to a specific database, run the following command:

\c database_name

• The \c command is used to switch to a specific database.

The \c command is similar to the USE command in MySQL.

Step 3 — List all tables in the database

To list all tables in the database, run the following command:

\dt

• The \dt command is used to list all tables in the database in the public schema.

The \dt command is similar to the SHOW TABLES; command in MySQL.

To list all tables in the database in all schemas, run the following command:

\dt *.*

• The *.* specifies that we want to list all tables in all schemas.

Conclusion

In this tutorial, you learned how to list all tables in PostgreSQL using the psql command-line tool.

To learn more about SQL, check out this free SQL eBook:

Introduction to SQL eBook

As a next step, I would recommend checking out Materialize. It is a streaming database that makes it easy to build real-time applications. It is the perfect database for building data-driven applications:

Materialize