How to Show Tables in Postgres

Introduction: In PostgreSQL, a popular and powerful open-source relational database management system, there are times when you need to retrieve a list of all tables within a database. This information is useful for understanding the structure of your database.

In this article, we will explore a few methods to show all database tables in PostgreSQL.

Using psql Command-Line Tool

1. Open your terminal or command prompt. Type the following command to access the psql interactive terminal:

psql -U your_username

Replace your_username with your PostgreSQL username.

2. Once you’re in the psql terminal, enter the following command to list all databases

\l

3. Then type this command to switch to the database

\c database_name

Replace database_name with one of the available databases.

4. Type the following to display a list of all tables in the current database:

\dt

Querying the Information Schema

1. Open your favorite PostgreSQL client, such as pgAdmin or any other database management tool.
2. Connect to your PostgreSQL database using the appropriate credentials.
3. Open a new query window or tab.
4. Execute the following SQL query:

SELECT table_name
FROM information_schema.tables
WHERE table_type = 'BASE TABLE'
  AND table_schema NOT IN ('pg_catalog', 'information_schema');

This query retrieves the names of all base tables from the information_schema.tables view, excluding system tables.

5. Execute the query, and the result will display a list of all tables within the database.

Using Luna Modeler to show tables in Postgres

Another option is to display all tables of the selected database using an ER diagram.

The advantage is greater clarity, the ability to search, view details, view SQL scripts and identify dependencies.

Creating an ER diagram in Luna Modeler is easy. Just create a database connection:

and then click on Connect and load existing structure.

Learn more about visualization of existing PostgreSQL databases.

Tip: you can split the diagram into sub-diagrams according to schemas, add notes and other information etc.

Conclusion

Retrieving a list of all database tables in PostgreSQL is essential for understanding the structure and contents of your database. In this article, we explored three methods: using the psql command-line tool, querying the information_schema and creating ER diagram using Luna Modeler.

Download Luna Modeler