Summary: this tutorial shows you different ways to show tables in PostgreSQL using psql tool and pg_catalog schema.
If you are coming from MySQL, you may miss the SHOW TABLES statement that displays all tables in a specific database. PostgreSQL does not provide the SHOW TABLES statement directly but give you something similar.
PostgreSQL show tables using psql
If you are using psql, you can use the following command to show tables in the current database.
1 | \dt |
For example, you can connect to the dvdrental database and show all tables as follows:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 | Server [localhost]: Database [postgres]: dvdrental Port [5432]: Username [postgres]: psql (9.4.2) dvdrental=# \dt List of relations Schema | Name | Type | Owner --------+---------------+-------+---------- public | actor | table | postgres public | address | table | postgres public | category | table | postgres public | city | table | postgres public | country | table | postgres public | customer | table | postgres public | film | table | postgres public | film_actor | table | postgres public | film_category | table | postgres public | inventory | table | postgres public | language | table | postgres public | payment | table | postgres public | persons | table | postgres public | rental | table | postgres public | staff | table | postgres public | store | table | postgres (16 rows) dvdrental=# |
PostgreSQL show tables using pg_catalog schema
Another way to show tables in PostgreSQL is to use SELECT statement to query data from the PostgreSQL catalog as follows:
1 2 3 4 5 6 7 | SELECT * FROM pg_catalog.pg_tables WHERE schemaname != 'pg_catalog' AND schemaname != 'information_schema'; |
We used condition in the WHERE clause to filter system tables. If you omit the WHERE clause, you will get many tables that are the system tables, which you may not want to.