Tables are the most often used database objects that help us store data in a well-organized (i.e., rows and columns) manner. PostgreSQL allows us to perform various operations on the tables, such as insertion, deletion, updation, and searching. While performing any of these tasks the Postgres users must determine the table’s structure. The table structure provides detailed information regarding table columns, constraints(if any), column types, etc.

This article explained how to get/check the table structure in PostgreSQL.

How to Get/Check Table Structure in Postgres?

In PostgreSQL, various methods are used to check the table’s structure. The following methods will be discussed to determine the table’s structure in Postgres:

- Method 1: Using “\d” Command
- Method 2: Using “\d+” Command
- Method 3: Using “information_schema”
- Method 4: Using the “SELECT *” Command

Method 1: Using “\d” Command

The “\d” is one of the most commonly used commands that retrieves the table’s structure:

\d emp_bio;

Here, “emp_bio” represents the targeted table:

The output shows the complete table structure, including column name, type, constraints, default value, etc.

Method 2: Using “\d+” Command

The “\d+” is an extended form of the “\d” command that retrieves some additional information:

\d+ emp_bio;

The stated command retrieves some extra information like “storage”, “description”, “access method”, etc.

Method 3: Using “information_schema”

Postgres supports another handy command that can be executed from any interface like psql or pgAdmin. Use the SELECT command with the “information_schema” to get the table’s structure:

SELECT *
FROM information_schema.columns
WHERE table_schema = 'public'
AND table_name = 'emp_bio';

In this example, the “public” illustrates the schema name while “emp_bio” represents the table name:

The output depicts that the information schema returns detailed information regarding the table’s structure, such as the “table_catalog”, “table_schema”, “data_type”, etc.

Method 4: Using the “SELECT *” Command

Type the SELECT * command is used with the “FALSE” option to get the table’s structure:

SELECT * FROM emp_bio
WHERE FALSE;

The primary use case of the stated command is fetching the table’s data. However, specifying the “FALSE” option in the WHERE clause will retrieve the table’s structure:

The stated command shows the column names, data types, and constraints of the selected table.

Conclusion

In PostgreSQL, the “\d” command, the “\d+” command, “information_schema”, and the “SELECT *” statements with the “FALSE” option are used to check the table’s structure. The “\d” and “\d+” are meta-commands and must be executed from the “SQL Shell” aka psql. While the “information_schema” and “SELECT *” commands can be executed from any Postgres tool, including psql and pgAdmin. This write-up has explained four different methods to get or check the table’s structure in Postgres.