PostgreSQL Describe Table

Updated May 17, 2023

Definition of PostgreSQL Describe Table

PostgreSQL describes a table is defined as check the structure of a table, we can describe the structure of a table by using \d and the table name command in PostgreSQL. In PostgreSQL describe table statement is not present like in MySQL instead of describe, we are using \d table name and \d+ table name. Describe table command gives the information of the name of column, data type of column, column modifiers information, index information, and foreign key constraint information.

Syntax:

Below is the syntax of describe table:

1. Describe the table using the command

\d name_of_table

Or

\d+ name_of_table

2. Describe the table using information schema

Select name_of_column1, name_of_column2, name_of_column3, …, name_of_columnN from information_schema.columns where condition;

Or

Select * from information_schema.columns where condition;

Parameter:

• \d: This is a metadata command used to describe table structure. It will give the information of the name of column, data type of column, collation, nullable, and default column.
• \d+: This is a metadata command used to describe table structure. It will give the information of name of column, data type of column, collation, nullable, storage, stats_target, description, and default column.
• Name of table: This is defined as the name of the table which we have used to describe structure. We can use any table with metadata and information schema in PostgreSQL.
• Select: This statement selects the specified column from the information schema columns tables. We can select single as well as multiple tables from the columns table.
• Name of column: Selecting the name of a column from the columns table in PostgreSQL defines the process of retrieving the column names. It allows us to select a single or multiple tables from the columns table.
• Information schema: This is defined as the information schema columns table used to select the column from the columns table. It will describe the information of the table.
• Columns: This is columns table used to describe the table structure in PostgreSQL. Columns table is very important while describing the table in PostgreSQL.
• Where condition: This condition is used to select the specified table to describe table structure in PostgreSQL. Where condition is an optional parameter while using a columns table in PostgreSQL.

How to Describe Table in PostgreSQL?

• To describe the table in PostgreSQL, we have no need to provide any privileges to the user, any user can describe the table.
• Below example shows that we have no need to provide any privilege to describe the table.
• In below the first example we check the privileges of a user, db_test user doesn’t have any privileges on the database server.
• In the second example, we are using the db_test user to describe a table, using the db_test user, it’s possible to describe the table in PostgreSQL because we do not need to give any privileges to describe the table.

psql -U postgres
\du
psql -U db_test -d testing
\d+ stud1

• Basically, we are using the below command to describe the table in PostgreSQL as follows:

\d
\d+
Select * from information_schema.columns;
Select column_name from information_schema.columns where table_name = 'name_of_table';

• We can use the metadata command and information_schema.columnscatalog table to describe the table in PostgreSQL.
• We can also use limit options to describe all the table from the information_schema.columnscatalog table. This catalog table is very important to fetch the information related to all the tables.
• We can also describe the table by using the pg_admin GUI tool.
• For describing the table in PostgreSQL, we need to connect the server using psql. Also, we can describe the table by using the OS command prompt.

Examples

Below are the example to describe a table.

Example #1

By using \d Command.

Below example shows that describe the table by using the \d command. We have describing the table name as stud1 by using \d command.

Command:

\d stud1

Example #2

By using \d+ Command.

Below example shows that describe the table by using the \d+ command. We are describing the table name as stud1 by using \d+ command.

Command:

\d+ stud1

Example #3

By using OS Command Prompt.

Below example shows that describe the table by using the OS command prompt. We have described the table name as stud1 using the OS command prompt.

Command:

psql -U postgres -d testing -c "\d+ stud1"

Example #4

Single Table by using information_schema.columns Table.

Below example shows that describe a single table by using the information_schema.columns table. We are describing the table name as stud1 by using the information_schema.columns table.

Command:

select * from information_schema.columns where table_name = 'stud1';

Example #5

Multiple Table by using information_schema.columns Table.

Below example shows that describe multiple table by using a columns table. Using information_schema.columns table, we are describing all tables in PostgreSQL.

Command:

select * from information_schema.columns;

We hope that this EDUCBA information on “PostgreSQL Describe Table” was beneficial to you. You can view EDUCBA’s recommended articles for more information.

1. PostgreSQL Compare Strings
2. PostgreSQL Copy Database
3. Text Search PostgreSQL
4. PostgreSQL OID