PostgreSQL Show Tables
Summary: in this tutorial, you will learn how to show tables in PostgreSQL usingpsql tool andpg_catalog schema.
MySQL offers a popularSHOW TABLES statement that displays all tables in a specific database.
Unfortunately, PostgreSQL does not support theSHOW TABLES statement directly but provides you with alternatives.
Showing tables from PostgreSQL using psql
First, open the Command Prompt on Windows or Terminal on Unix-like systems and connect to the PostgreSQL using psql client tool:
psql -U postgresSecond, change the current database to the one that you want to show tables:
\c dvdrentalNote that you can connect to a specific database when you log in to the PostgreSQL database server:
psql -U postgres -d dvdrentalIn this command, the-d flag meansdatabase. In this command, you connect to thedvdrental database using thepostgres user.
Third, use the\dt command from the PostgreSQL command prompt to show tables in thedvdrental database:
\dtOutput:
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 | rental | table | postgres public | staff | table | postgres public | store | table | postgres(15 rows)To get more information on tables, you can use the\dt+ command. It will add thesize anddescription columns:
\dt+Output:
List of relations Schema | Name | Type | Owner | Persistence | Access method | Size | Description--------+---------------+-------+----------+-------------+---------------+------------+------------- public | actor | table | postgres | permanent | heap | 40 kB | public | address | table | postgres | permanent | heap | 88 kB | public | category | table | postgres | permanent | heap | 8192 bytes | public | city | table | postgres | permanent | heap | 64 kB | public | country | table | postgres | permanent | heap | 8192 bytes | public | customer | table | postgres | permanent | heap | 96 kB | public | film | table | postgres | permanent | heap | 736 kB | public | film_actor | table | postgres | permanent | heap | 272 kB | public | film_category | table | postgres | permanent | heap | 72 kB | public | inventory | table | postgres | permanent | heap | 232 kB | public | language | table | postgres | permanent | heap | 8192 bytes | public | payment | table | postgres | permanent | heap | 896 kB | public | rental | table | postgres | permanent | heap | 1232 kB | public | staff | table | postgres | permanent | heap | 16 kB | public | store | table | postgres | permanent | heap | 8192 bytes |(15 rows)To show the details of a specific table, you can specify the name of the table after the \d command:
\d table_nameOr
\d+ table_nameFor example, the following shows the structure of the actor table:
\d actorOutput:
Table "public.actor" Column | Type | Collation | Nullable | Default-------------+-----------------------------+-----------+----------+----------------------------------------- actor_id | integer | | not null | nextval('actor_actor_id_seq'::regclass) first_name | character varying(45)| | not null | last_name | character varying(45)| | not null | last_update | timestamp without time zone | | not null | now()Indexes: "actor_pkey" PRIMARY KEY, btree (actor_id) "idx_actor_first_name" btree (first_name) "idx_actor_last_name" btree (last_name)Referenced by: TABLE "film_actor" CONSTRAINT "film_actor_actor_id_fkey" FOREIGN KEY (actor_id) REFERENCES actor(actor_id) ON UPDATE CASCADE ON DELETE RESTRICTTriggers: last_updated BEFORE UPDATE ON actor FOR EACH ROW EXECUTE FUNCTION last_updated()Showing tables using pg_catalog schema
The following statement retrieves the table in PostgreSQL from thepg_catalog.pg_tables view:
SELECT*FROM pg_catalog.pg_tablesWHERE schemaname!= 'pg_catalog' AND schemaname!= 'information_schema';Output:
schemaname | tablename | tableowner | tablespace | hasindexes | hasrules | hastriggers | rowsecurity------------+---------------+------------+------------+------------+----------+-------------+------------- public | actor | postgres | null | t | f | t | f public | store | postgres | null | t | f | t | f public | address | postgres | null | t | f | t | f public | category | postgres | null | t | f | t | f public | city | postgres | null | t | f | t | f public | country | postgres | null | t | f | t | f public | customer | postgres | null | t | f | t | f public | film_actor | postgres | null | t | f | t | f public | film_category | postgres | null | t | f | t | f public | inventory | postgres | null | t | f | t | f public | language | postgres | null | t | f | t | f public | rental | postgres | null | t | f | t | f public | staff | postgres | null | t | f | t | f public | payment | postgres | null | t | f | t | f public | film | postgres | null | t | f | t | f(15 rows)In this query, we use a condition in theWHERE clause to exclude the system tables. If you omit theWHERE clause, you will get many tables including the system ones.
Summary
- Use the
\dtor\dt+command inpsqlto show tables in a specific database. - Use the
SELECTstatement to query table information from thepg_catalog.pg_tablescatalog.
Last updated on