PostgreSQL – Describe Table

In PostgreSQL, there is no DESCRIBE statement as in MySQL. But users can query the information on columns of a table in a couple of ways. In this article we will look into them.

Using the pSQL shell:

The information on various columns of a table can be achieved any of the below commands:

Syntax: \d or \d+

Example:

In this example, we will describe the table city of the sample database, ie, dvdrental:

First log into the PostgreSQL server using the pSQL shell:

Now shift to the dvdrental database using the below command:

\c dvdrental

Now use the below command to describe the city table:

\d city;

This will result in the below:

The below command can also be used for the same purpose:

\d+ city

Output:

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.

Syntax:
SELECT 
   table_name, 
   column_name, 
   data_type 
FROM 
   information_schema.columns
WHERE 
   table_name = 'table_name';

Example:

Use the below statement to get information on the film table of the dvdrental database:

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

Output: