Listing tables in a database in PostgreSQL

PostgreSQL provides two methods to list all tables in a database:

• Use \dt or \dt+ in the psql tool to list all the tables currently in the current database.
• Query all tables from the pg_tables table.

Use \dt list the tables in a database

This example demonstrates the steps of logging in to the database and listing the tables in the database using the psql tool. Please follow the steps below:

1. Log in to the PostgreSQL server as the postgres user:

   [~] psql -U postgres
   psql (14.4)
   Type "help" for help.
   

   Note: You can also log in as any other user with appropriate database privileges.

2. Connect to the testdb database:

   \c testdb;
   

   If you haven’t created the database yet, run the following statement first:

   CREATE DATABASE testdb;
   

3. List all tables in the testdb database using the \dt command as follows:

   \dt
   

              List of relations
   Schema |      Name      | Type  |  Owner
   --------+----------------+-------+----------
   public | mytable        | table | postgres
   public | product        | table | postgres
   public | test_date      | table | postgres
   public | test_time      | table | postgres
   public | test_timestamp | table | postgres
   public | week_day_sales | table | postgres
   (6 rows)

4. If you want to see more information about the table, use the \dt+ command as follows:

   \dt+
   

                                            List of relations
   Schema |      Name      | Type  |  Owner   | Persistence | Access method |    Size    | Description
   --------+----------------+-------+----------+-------------+---------------+------------+-------------
   public | mytable        | table | postgres | permanent   | heap          | 16 kB      |
   public | product        | table | postgres | permanent   | heap          | 16 kB      |
   public | test_date      | table | postgres | permanent   | heap          | 8192 bytes |
   public | test_time      | table | postgres | permanent   | heap          | 8192 bytes |
   public | test_timestamp | table | postgres | permanent   | heap          | 8192 bytes |
   public | week_day_sales | table | postgres | permanent   | heap          | 8192 bytes |
   (6 rows)

   You can see that there are three more columns in the output: Persistence, Access method, Size and Description.

Listing tables from the pg_tables table

In addition to the \dt or \dt+ command above, you can also query all tables in the current data from the pg_tables table.

The pg_tables table is a built-in table in PostgreSQL, which stores all the tables in the database.

SELECT * FROM pg_tables
WHERE schemaname = 'public';

schemaname |   tablename    | tableowner | tablespace | hasindexes | hasrules | hastriggers | rowsecurity
------------+----------------+------------+------------+------------+----------+-------------+-------------
public     | test_date      | postgres   |            | t          | f        | f           | f
public     | test_time      | postgres   |            | t          | f        | f           | f
public     | test_timestamp | postgres   |            | t          | f        | f           | f
public     | week_day_sales | postgres   |            | t          | f        | f           | f
public     | mytable        | postgres   |            | f          | f        | f           | f
public     | product        | postgres   |            | t          | f        | f           | f
(6 rows)

Conclusion

PostgreSQL provides two methods to list all tables in a database:

• Use \dt or \dt+ in the psql tool to list all the tables currently in the current database.
• Query all tables from the pg_tables table.

In MySQL, you can use the SHOW TABLES command to list databases.