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: