Question

How to show all tables in PostgreSQL?

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 post, I will show you how to list all tables in PostgreSQL using the psql command-line tool.


Submit an answer

This textbox defaults to using Markdown to format your answer.

You can type !ref in this text area to quickly search our full set of tutorials, documentation & marketplace offerings and insert the link!

Sign In or Sign Up to Answer

These answers are provided by our Community. If you find them useful, show some love by clicking the heart. If you run into issues leave a comment, or add your own answer to help others.

Accepted Answer

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