How to List All Tables in PostgreSQL Database

If you are new to PostgreSQL and databases, you might wonder how to see a list of all the tables in your database. This tutorial will guide you through the steps to list all tables in PostgreSQL, using simple English and easy-to-follow instructions.

Tables in a database are like different sections in a filing cabinet. Each table holds specific types of information. Sometimes, you need to see all the tables to understand what data you have or to find the information you need.

Advertisement

Methods to Show Tables in PostgreSQL Database

Use one of the following methods (CLI or pgAdmin) to show tables in a Postgres database.

1. Using CLI (Command Line Interface)

Step 1: Log in as the Admin User

Open your command prompt or terminal and type the following command to log in as the admin user (usually postgres):

psql -U postgres

Enter your password if prompted.

Step 2: Connect to Your Database

Once logged in as the admin user (you should see postgres=#), type the following command to connect to your database:

\c yourdatabase

Replace yourdatabase with the name of your database.

Press Enter. You should now be connected to your database (you should see yourdatabase=#).

Step 3: List All Tables in the Public Schema

Type the following command to list all tables in the public schema:

\dt

Press Enter. You will see a list of all tables in the public schema.

Step 4: List All Tables Not in the Public Schema

Type the following command to list all tables that are not in the public schema:

\dt *.*

Press Enter. You will see a list of all tables in the database, including those not in the public schema. Look for tables with different schema names. Example Output:

        List of relations
 Schema  |       Name        | Type  |  Owner
---------+-------------------+-------+----------
 public  | customers         | table | postgres
 public  | orders            | table | postgres
 public  | products          | table | postgres
 sales   | sales_data        | table | postgres
 hr      | employee_records  | table | postgres

Step 5: List Tables using Query (Optional)

You can also access all tables using the following SQL statment using programmatically or at psql terminal.

SELECT * FROM pg_catalog.pg_tables;

2. Using pgAdmin

The pgAdmin users can also check all tables using the following steps:

Step 1: Open pgAdmin

1. Open pgAdmin.
2. Connect to your database server.
3. Select your database from the list on the left.

Step 2: List All Tables

1. In the pgAdmin interface, go to the left sidebar.
2. Expand the database you are working with by clicking the plus (+) sign next to it.
3. Expand the “Schemas” folder.
4. Expand the “public” folder (or the schema you are using).
5. Click on the “Tables” folder. You will see a list of all tables in the main area.

Conclusion

Now you know how to list all the tables in your PostgreSQL database using both the command line interface and pgAdmin! This is a useful skill for managing and understanding your data. Whether you prefer using the CLI or a graphical interface, the steps are simple and straightforward. Happy learning!