PostgreSQL Describe Table

Definition of PostgreSQL Describe Table

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

Syntax:

Below is the syntax of describe table in PostgreSQL:

1. Describe table using 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 metadata command used to describe table structure. It will give the information of name of column, data type of column, collation, nullable, and default column.
• \d+: This is 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 name of table which we have used to describe structure. We can use any table with metadata and information schema in PostgreSQL.
• Select: This statement is used to select the specified column from the information schema columns tables. We can select single as well as multiple table from the columns table.
• Name of column: This is defined as select name of column from the columns table in PostgreSQL.We can select single as well as multiple table from the columns table.
• Information schema: This is defined as information schema columns table used to select the column from the columns table. It will describe the information of table.
• Columns: This is columns table used to describe the table structure in PostgreSQL. Columns table is very important while describing table in PostgreSQL.
• Where condition: This condition is used to select the specified table to describe table structure in PostgreSQL. Where condition is optional parameter while using 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 PostgreSQL.
• In below first example we check privileges of a user, db_test user doesn’t have any privileges on the database server.
• In second example we are using db_test user to describe a table, using db_test user it’s possible to describe the table in PostgreSQL. Because we have no need to give any privileges to describe the table in PostgreSQL.

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

• Basically, we are using 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 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 pg_admin GUI tool in PostgreSQL.
• For describing table in PostgreSQL first we need to connect the server using psql. Also, we can describe the table by using OS command prompt.

Examples

Below are the example to describe table in PostgreSQL.

Example #1

By using \d Command.

Below example shows that describe the table by using the \d command. We have describing 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 describing table name as stud1 by using 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 information_schema.columns table. We are describing table name as stud1 by using 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 columns table. Using information_schema.columns table we are describing all tables in PostgreSQL.

Command:

select * from information_schema.columns;

This is a guide to PostgreSQL Describe Table. Here we discuss the definition, syntax, parameters, and how to describe table in PostgreSQL with examples. You may also have a look at the following articles to learn more –

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