PostgreSQL: Describe Table

In this post, I am going to explain different approaches to get the information about a table.

 

Step 1: Using \d+  or \d command.

 

\d+ table_name
\d table_name

 

Login to Postgres shell.

sh-3.2$ psql -U postgres -h localhost --dbname=test
Password for user postgres: 
psql (14.4)
Type "help" for help.

 

Let’s see the tables in test database using the command \d.

test=# \d
           List of relations
 Schema |   Name    | Type  |  Owner   
--------+-----------+-------+----------
 public | contact   | table | postgres
 public | employees | table | postgres
(2 rows)

Let’s get the description of contact table using \d+ command.

test=# \d+ contact
                                                   Table "public.contact"
   Column   |          Type          | Collation | Nullable | Default | Storage  | Compression | Stats target | Description 
------------+------------------------+-----------+----------+---------+----------+-------------+--------------+-------------
 id         | integer                |           | not null |         | plain    |             |              | 
 age        | integer                |           |          |         | plain    |             |              | 
 firstname  | character varying(255) |           |          |         | extended |             |              | 
 lastname   | character varying(255) |           |          |         | extended |             |              | 
 middlename | character varying(255) |           |          |         | extended |             |              | 
Indexes:
    "contact_pkey" PRIMARY KEY, btree (id)
Access method: heap

 

 

\d table command return minified version of \d+ output

test=# \d contact
                        Table "public.contact"
   Column   |          Type          | Collation | Nullable | Default 
------------+------------------------+-----------+----------+---------
 id         | integer                |           | not null | 
 age        | integer                |           |          | 
 firstname  | character varying(255) |           |          | 
 lastname   | character varying(255) |           |          | 
 middlename | character varying(255) |           |          | 
Indexes:
    "contact_pkey" PRIMARY KEY, btree (id)

Approach 2: By querying information_schema

 

Syntax

SELECT 
   *
FROM 
   information_schema.columns
WHERE 
   table_name = {TABLE_NAME};

Example

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

test=# SELECT table_name, column_name, column_default, is_nullable, data_type FROM information_schema.columns WHERE table_name = 'contact';
 table_name | column_name | column_default | is_nullable |     data_type     
------------+-------------+----------------+-------------+-------------------
 contact    | id          |                | NO          | integer
 contact    | age         |                | YES         | integer
 contact    | firstname   |                | YES         | character varying
 contact    | lastname    |                | YES         | character varying
 contact    | middlename  |                | YES         | character varying
(5 rows)

Previous                                                 Next                                                 Home