PostgreSQL Describe Tables

PostgreSQL provides two ways to view the definition or structure of an existing table:

• Use \d or \d+ command in the psql tool to list all tables in the current database .
• Query columns of a table from information_schema.columns.

Use \d to view the information of the table

This example demonstrates the detailed steps of using the psql tool log in to the database and view tables information. Please follow the steps below:

1. Log in to the PostgreSQL server as the postgres user:

   [~] psql -U postgres
   psql (14.4)
   Type "help" for help.
   

   Note: You can also log in as any other user with appropriate database privileges.

2. connnect to the testdb database:

   \c testdb;
   

   If you haven’t created the database yet, run the following statement first:

   CREATE DATABASE testdb;
   

3. The following statement uses the \d command to view the structure of the test_date table, as follows:

   \d test_date
   

                          Table "public.test_date"
      Column   |  Type   | Collation | Nullable |           Default
   ------------+---------+-----------+----------+------------------------------
    id         | integer |           | not null | generated always as identity
    date_value | date    |           | not null | CURRENT_DATE
   Indexes:
      "test_date_pkey" PRIMARY KEY, btree (id)

   You can see that the name of the table, the columns in the table, the constraints in the table and other information.

4. If you want to see more information about the test_date table, use the \d+ command as follows:

   \d+ test_date
   

                                                     Table "public.test_date"
      Column   |  Type   | Collation | Nullable |           Default            | Storage | Compression | Stats target | Description
   ------------+---------+-----------+----------+------------------------------+---------+-------------+--------------+-------------
    id         | integer |           | not null | generated always as identity | plain   |             |              |
    date_value | date    |           | not null | CURRENT_DATE                 | plain   |             |              |
   Indexes:
       "test_date_pkey" PRIMARY KEY, btree (id)
   Access method: heap

   As you can see, there \d+are more inputs than \doutputs Compression, Stats target and Description columns.

Query all columns in a table from information_schema

The information_schema is a system-level Schema, which provides some usefull views to query information such as tables, columns, indexes, and functions.

The information_schema.columns catalog contains information about the columns of all tables.

The following statement queries all the columns of the test_date table from the information_schema.columns table:

SELECT
   table_name,
   column_name,
   data_type,
   column_default
FROM
   information_schema.columns
WHERE
   table_name = 'test_date';

table_name | column_name | data_type | column_default
------------+-------------+-----------+----------------
test_date  | id          | integer   |
test_date  | date_value  | date      | CURRENT_DATE
(2 rows)

The above statement returns the information of all the columns of the test_date table, including column names, data types, and default values.

Conclusion

PostgreSQL provides two ways to view the definition or structure of an existing table:

• Use \d or \d+ command in the psql tool to list all tables in the current database .
• Query columns of a table from information_schema.columns.

In MySQL, you can use the DESCRIBE command list to view the columns in a table.