August 7, 2023
Summary: in this tutorial, you will learn how to show tables in PostgreSQL using
psql tool and
If you are coming from MySQL, you may want to use the popular
SHOW TABLES statement that displays all tables in a specific database.
PostgreSQL does not support the
SHOW TABLES statement directly but provides you with an alternative.
Showing tables from PostgreSQL using psql
First, connect to PostgreSQL using the psql tool.
$ psql -U postgres -W
-U flag stands for the user and
-W option requires you to provide the password. In this command, you use the
postgres user to log in to the PostgreSQL database server.
Second, enter the password for the user
postgres and press the
Password for user postgres: postgres=#
Third, switch to a database e.g..,
postgres=# \c dvdrental You are now connected to database "dvdrental" as user "postgres".
Note that you can connect to a specific database when you log in to the PostgreSQL database server:
$ psql -U postgres -d dvdrental
In this command, the
-d flag means database. In this command, you connect to the
dvdrental database using the
Third, use the
\dt command from the PostgreSQL command prompt to show tables in the
To get more information on tables, you can use the
\dt+ command. It will add the
Showing tables using pg_catalog schema
Another way to show tables in PostgreSQL is to use the
SELECT statement to query data from the PostgreSQL catalog as follows:
SELECT * FROM pg_catalog.pg_tables WHERE schemaname != 'pg_catalog' AND schemaname != 'information_schema';
In this query, we used a condition in the
WHERE clause to filter system tables. If you omit the
WHERE clause, you will get many tables including the system tables.
- Use the
psqlto show tables in a specific database.
- Use the
SELECTstatement to query table information from the