In database management systems like PostgreSQL, describing tables provides us with detailed information about the tables of a specific database. For this purpose, different commands and queries are used in Postgres, such as “\d”, “\d+”, information schema, etc. These commands and queries allow us to describe a specific table or all the tables of a database. Postgres users can execute these commands from the SQL Shell(psql).

This Post presents a practical guide on describing the Postgres tables using SQL Shell. This post will discuss the following methods of describing the tables using SQL Shell:

• How to Describe All the Postgres Tables Using “\d” Command in SQL Shell?
• How to Describe All the Postgres Tables Using “\d+” Command in SQL Shell?
• How to Describe a Specific Postgres Table Using “\d” Command in SQL Shell?
• How to Describe a Specific Postgres Table Using “information_schema” in psql?

How to Describe All the Postgres Tables Using “\d” Command in SQL Shell?

Executing the “\d” command from SQL Shell will describe all the tables of the selected database. Here is an example:

\d

The output shows that the “\d” command describes all the relations of the “postgres” database. It retrieves the schema name, table name, type, and owner.

How to Describe All the Postgres Tables Using “\d+” Command in SQL Shell?

Alternatively, you can use the “\d+” command to describe all the tables with more details like table size, access method, persistence, etc.

\d+

How to Describe a Specific Postgres Table Using “\d” Command in SQL Shell?

The “\d” command can also be used to describe only a specific table in Postgres. It will retrieve the complete structure of the selected table. To do that, all you need to do is, specify the “\d” command followed by the table name. For instance, the below statement describes the “employee_info” table:

\d employee_info;

The output signifies that the “\d” command describes the complete structure of the selected table, such as column name, type, default value, etc. Moreover, it also provides details regarding the table constraints, such as primary, foreign key constraints, etc.

Note: You can also use the “\d+” Command followed by the table name to describe the table with more details, such as access method, description, etc.

How to Describe All the Postgres Tables Using “information_schema” in psql?

You can also use the built-in information_schema to describe a specific table or all the schema tables.

SELECT COLUMN_NAME, DATA_TYPE
FROM information_schema.columns;

In the above snippet, we described all the tables' column names and data types using information_schema.

How to Describe a Specific Postgres Table Using “information_schema” in psql?

You can also use the information_schema to describe only a specific table. For this purpose, you must specify the table’s name in the WHERE clause:

SELECT COLUMN_NAME, DATA_TYPE
FROM information_schema.columns
WHERE table_name = 'employee_info';

This way, you can describe a specific table using the built-in ‘information_schema”.

Conclusion

SQL Shell supports various commands and queries to describe the Postgres tables, such as “\d”, “\d+”, information_schema, etc. These commands and queries allow us to describe a specific table or all the tables of a database. The “\d+” command describes all the tables with more details like table size, access method, persistence, etc. This post explained several methods to describe the Postgres tables using SQL Shell.