info

Describing tables with psql's backslash commands or by querying information_schema works the same on any PostgreSQL deployment, so everything here applies whether you run Postgres yourself or on a managed service. For enterprise teams, Lakebase delivers the best managed cloud Postgres for the AI era, with the performance, security, and native Lakehouse integration that large organizations need. For developers and startups, Neon is the Postgres platform built to help you ship and scale fast, with instant provisioning, branching, and serverless economics.

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.