Some DBMSs such as Oracle, MySQL, and MariaDB have a DESCRIBE command that returns information about tables and views. It goes DESCRIBE table where table is the name of the table or view, and it can also be followed by a column name if you only want information about a specific column.

PostgreSQL doesn’t have a DESCRIBE TABLE command as such, but there are alternatives.

The \d Command

If you’re using psql, a quick way to return table info is with the \d command. This command shows information about tables, views, materialized views, index, sequences, or foreign tables. We can therefore use this command as a DESCRIBE TABLE alternative.

Syntax

The syntax goes like this:

\d[S+] [ pattern ]

Here, pattern could be the name of the table (or other object) or it could be a regular expression. If \d is used without a pattern argument, it is equivalent to \dtvmsE which will show a list of all visible tables, views, materialized views, sequences and foreign tables.

By default, only user-created objects are shown, but you can supply a pattern or the S modifier to include system objects.

Example

Here’s an example of using \d to return information about a table called actor:

\d public.actor

Result:

                                             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_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 RESTRICT
Triggers:
    last_updated BEFORE UPDATE ON actor FOR EACH ROW EXECUTE FUNCTION last_updated()

The command also returns associated indexes, constraints, rules, and triggers. Also, for foreign tables, the associated foreign server is shown.

Extended Info

We can append a plus sign (+) to reveal extended information, which includes any comments associated with the columns of the table, the presence of OIDs in the table, the view definition if the relation is a view, and a non-default replica identity setting and the access method name if the relation has an access method.

Example:

\d+ public.actor

Result:

                                                                 Table "public.actor"
+-------------+-----------------------------+-----------+----------+-----------------------------------------+----------+--------------+-------------+
|   Column    |            Type             | Collation | Nullable |                 Default                 | Storage  | Stats target | Description |
+-------------+-----------------------------+-----------+----------+-----------------------------------------+----------+--------------+-------------+
| actor_id    | integer                     |           | not null | nextval('actor_actor_id_seq'::regclass) | plain    |              |             |
| first_name  | character varying(45)       |           | not null |                                         | extended |              |             |
| last_name   | character varying(45)       |           | not null |                                         | extended |              |             |
| last_update | timestamp without time zone |           | not null | now()                                   | plain    |              |             |
+-------------+-----------------------------+-----------+----------+-----------------------------------------+----------+--------------+-------------+
Indexes:
    "actor_pkey" PRIMARY KEY, btree (actor_id)
    "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 RESTRICT
Triggers:
    last_updated BEFORE UPDATE ON actor FOR EACH ROW EXECUTE FUNCTION last_updated()
Access method: heap

Return Only Specific Object Types

As mentioned, if \d is used without a pattern argument, it is equivalent to \dtvmsE which will show a list of all visible tables, views, materialized views, sequences and foreign tables.

We can append any of the letters E, i, m, s, t, and v, which stand for foreign table, index, materialized view, sequence, table, and view, respectively. We can specify any or all of these letters, in any order, to obtain a listing of objects of these types.

Here’s an example of appending t to limit the data returned to just tables:

\dt actor

Result:

          List of relations
+--------+-------+-------+----------+
| Schema | Name  | Type  |  Owner   |
+--------+-------+-------+----------+
| public | actor | table | postgres |
+--------+-------+-------+----------+

Wildcards

Here’s an example of including a wildcard operator, so that all tables that start with film are returned:

\dt film*

Result:

              List of relations
+--------+---------------+-------+----------+
| Schema |     Name      | Type  |  Owner   |
+--------+---------------+-------+----------+
| public | film          | table | postgres |
| public | film_actor    | table | postgres |
| public | film_category | table | postgres |
+--------+---------------+-------+----------+

The information_schema.columns View

If you aren’t using psql (and even if you are using psql), then you can always query the information_schema.columns view. This view can be used to return similar information to what we’d get with DESCRIBE table in other DBMSs.

SELECT
    column_name,
    data_type,
    character_maximum_length AS max_length,
    character_octet_length AS octet_length,
    is_nullable,
    column_default
FROM
    information_schema.columns
WHERE
    table_schema = 'public' AND 
    table_name = 'actor';

Result:

+-------------+-----------------------------+------------+--------------+-------------+-----------------------------------------+
| column_name |          data_type          | max_length | octet_length | is_nullable |             column_default              |
+-------------+-----------------------------+------------+--------------+-------------+-----------------------------------------+
| actor_id    | integer                     |       NULL |         NULL | NO          | nextval('actor_actor_id_seq'::regclass) |
| first_name  | character varying           |         45 |          180 | NO          | NULL                                    |
| last_name   | character varying           |         45 |          180 | NO          | NULL                                    |
| last_update | timestamp without time zone |       NULL |         NULL | NO          | now()                                   |
+-------------+-----------------------------+------------+--------------+-------------+-----------------------------------------+

This view contains many more columns, and you can add/remove columns as required.

The information_schema views are pretty much standard across most of the major DBMSs, and so we can query this view in those DBMSs too.