PostgreSQL Describe Table

Summary: in this tutorial, you will learn how to use the psql tool and information_schema to describe tables in PostgreSQL.

If you have been using MySQL, you typically use the DESCRIBE statement to find the information about a table.

PostgreSQL does not support the DESCRIBEstatement. However, you can query the information in columns of a table in a couple of ways.

1) PostgreSQL DESCRIBE TABLE using psql

First, connect to the PostgreSQL server using the psql tool:

psql -U postgres

It’ll prompt you to enter a password for the postgres user.

Second, change the current database to dvdrental sample database:

\c dvdrental

Third, execute the \d table_name to or \d+ table_name to show the structure of a table. For example, the following shows the structure of the film table in the sample database:

\d film

Output:

                                              Table "public.film"
      Column      |            Type             | Collation | Nullable |                Default
------------------+-----------------------------+-----------+----------+---------------------------------------
 film_id          | integer                     |           | not null | nextval('film_film_id_seq'::regclass)
 title            | character varying(255)      |           | not null |
 description      | text                        |           |          |
 release_year     | year                        |           |          |
 language_id      | smallint                    |           | not null |
 rental_duration  | smallint                    |           | not null | 3
 rental_rate      | numeric(4,2)                |           | not null | 4.99
 length           | smallint                    |           |          |
 replacement_cost | numeric(5,2)                |           | not null | 19.99
 rating           | mpaa_rating                 |           |          | 'G'::mpaa_rating
 last_update      | timestamp without time zone |           | not null | now()
 special_features | text[]                      |           |          |
 fulltext         | tsvector                    |           | not null |
Indexes:
    "film_pkey" PRIMARY KEY, btree (film_id)
    "film_fulltext_idx" gist (fulltext)
    "idx_fk_language_id" btree (language_id)
    "idx_title" btree (title)
Foreign-key constraints:
    "film_language_id_fkey" FOREIGN KEY (language_id) REFERENCES language(language_id) ON UPDATE CASCADE ON DELETE RESTRICT
Referenced by:
    TABLE "film_actor" CONSTRAINT "film_actor_film_id_fkey" FOREIGN KEY (film_id) REFERENCES film(film_id) ON UPDATE CASCADE ON DELETE RESTRICT
    TABLE "film_category" CONSTRAINT "film_category_film_id_fkey" FOREIGN KEY (film_id) REFERENCES film(film_id) ON UPDATE CASCADE ON DELETE RESTRICT
    TABLE "inventory" CONSTRAINT "inventory_film_id_fkey" FOREIGN KEY (film_id) REFERENCES film(film_id) ON UPDATE CASCADE ON DELETE RESTRICT
Triggers:
    film_fulltext_trigger BEFORE INSERT OR UPDATE ON film FOR EACH ROW EXECUTE FUNCTION tsvector_update_trigger('fulltext', 'pg_catalog.english', 'title', 'description')
    last_updated BEFORE UPDATE ON film FOR EACH ROW EXECUTE FUNCTION last_updated()

The command returns a lot of information on the structure of the film table. Additionally, it returns indexes, foreign key constraints, and triggers.

2) PostgreSQL DESCRIBE TABLE using information_schema

The information_schema.columns catalog contains the information on columns of all tables. To get information on columns of a table, you query the information_schema.columns catalog.

For example:

SELECT
  column_name,
  data_type,
  character_maximum_length,
  is_nullable,
  column_default
FROM
  information_schema.columns
WHERE
  table_name = 'film';

Output:

   column_name    |          data_type          | character_maximum_length | is_nullable |            column_default
------------------+-----------------------------+--------------------------+-------------+---------------------------------------
 film_id          | integer                     |                     null | NO          | nextval('film_film_id_seq'::regclass)
 title            | character varying           |                      255 | NO          | null
 description      | text                        |                     null | YES         | null
 release_year     | integer                     |                     null | YES         | null
 language_id      | smallint                    |                     null | NO          | null
 rental_duration  | smallint                    |                     null | NO          | 3
 rental_rate      | numeric                     |                     null | NO          | 4.99
 length           | smallint                    |                     null | YES         | null
 replacement_cost | numeric                     |                     null | NO          | 19.99
 rating           | USER-DEFINED                |                     null | YES         | 'G'::mpaa_rating
 last_update      | timestamp without time zone |                     null | NO          | now()
 special_features | ARRAY                       |                     null | YES         | null
 fulltext         | tsvector                    |                     null | NO          | null
(13 rows)

Note that the SELECT * from the information_schema.columns will retrieve a comprehensive set of information.

Summary

• Use the \d table_name to show the structure of the table using psql.
• Query data from the information_schema.columns to retrieve the column information.