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 have 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 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 gives 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 gives 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 was we have used to describing structure. We can use any table with metadata and information schema in PostgreSQL.
- Select –This statement is used to select specified column from the information schema columnstables. 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 describe the table structure in PostgreSQL. Columns table is very important while describing table in PostgreSQL.
- Where condition –This condition is used to select 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?
- Below is the syntax 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 have check privileges of a user, db_test user doesn’t have any privileges on database server.
- In second example we have using db_test user to describing a table, using db_test user it’s possible to describe the table in PostgreSQL. Because we have no need to given any privileges to describe the table in PostgreSQL.
psql -U postgres
\du
psql -U db_test -d testing
\d+ stud1
- Basically we have using below command to describe the table in PostgreSQL are 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 metadata command and information_schema.columnscatalog table to describe 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 is 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.
\d stud1
Example #2: 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.
\d+ stud1
Example #3: By using OS Command Prompt
Below example shows that describe the table by using OS command prompt. We have describing table name as stud1 by using OS command prompt.
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.
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 have describing all tables in PostgreSQL.
select * from information_schema.columns;
Recommended Articles
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 –
