PostgreSQL – Describe Table
Unlike MySQL, PostgreSQL does not have a ‘DESCRIBE’ statement to view table column details. However, PostgreSQL provides several methods to access information about table columns.
In this article, we’ll learn two effective ways to Describe Tables in PostgreSQL.
1. Using the pSQL shell
The ‘psql’ command-line interface in PostgreSQL offers commands to describe table structures. The information on various columns of a table can be achieved by 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:
Explanation: The output of ‘\d city’ or ‘\d+ city’ will include columns, types, modifiers, and constraints.
2. 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.
Query:
SELECT table_name, column_name, data_type FROM information_schema.columns WHERE table_name = 'film';
Output:
Explanation: The result set will include columns such as ‘table_name’, ‘column_name’, and ‘data_type’, providing a clear overview of the table structure.
