How to show all tables in PostgreSQL?

How to show all tables in PostgreSQL?

Written by Bobby Iliev on Nov 7th, 2022 ・ Views ・ Report Post

Bobby Iliev Visit User Profile

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:

🤩 Our Amazing Sponsors 👇
DigitalOcean View Website
DigitalOcean offers a simple and reliable cloud hosting solution that enables developers to get their website or application up and running quickly.
Laravel News View Website
Laravel News keeps you up to date with everything Laravel. Everything from framework news to new community packages, Laravel tutorials, and more.
Genesis View Website
A Laravel Starter Kit that includes Authentication, User Dashboard, Edit Profile, and a set of UI Components. 
\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

Comments (0)