In PostgreSQL, describing a table means checking the table’s structure or getting all the information about a table. PostgreSQL offers several ways to describe a table. For example, executing the “\d” or “\dt” command from the SQL SHELL or using information_schema in pgAdmin 4.

Let’s learn how to describe a table in PostgreSQL with the help of examples.

How to Describe a Table in PostgreSQL Using SQL SHELL(psql)?

Let’s go through the below-listed steps to learn how to describe a table in Postgres using SQL SHELL.

Step 1: Run “\c” Command to Establish a Connection With a Database

Firstly, open psql, provide the required details, and run the “\c” command followed by database name to connect to a specific database:

\c example;

The above snippet shows that you are successfully connected to the desired database.

Step 2: Run “\d” Command to Describe All Tables Including System Schemas

Let’s execute the “\d” command to see all the available relations within the example database:

\d;

The \d command fetches all the relations, including those that belong to system schemas.

Step 3: Run “\dt” Command to Describe User-defined Tables

In the above output, we observed that the “\d” command fetched all the tables, including the system’s schemas. To describe only user-defined tables, run the “\dt“ command:

\dt;

The above snippet shows that the “\dt” fetched only user-defined tables and skipped the system schemas.

Step 4: Run “\d” Command With Table Name to Describe a Specific Tables

Suppose we have to describe the bike_details table. To do so, we can run the “\d” command followed by the table name i.e. “bike_details”:

\d bike_details;

Executing the “\d” command with the table name successfully described the table details such as column name, data type, primary key, default value, and so on.

How to Describe a Table in PostgreSQL Using pgAdmin 4?

PostgreSQL offers a build-in schema named information_schema that is common to every database. The basic syntax of the information_schema will be as follows:

 SELECT col_1, col_2, ..., col_N
 FROM information_schema.COLUMNS
 WHERE Condition;

- The SELECT query will select/fetch all the specified columns.

- col_1, col_2, … are the columns to be selected.

- The information_schema is an inbuilt schema used to describe the table’s information.

- WHERE is an optional clause that is used to specify a condition.

- Condition represents criteria based on which the selected tables will be described.

The below-given examples will explain this concept with more clarity.

Firstly, you have to open the query tool to run the queries from pgAdmin. To do that, right-click on the selected database and then click on the “Query Tool” as shown below:

Clicking on the Query Tool will open the below-given window:

Here, in the query editor, you can execute any command/query of your choice.

Example #1: How to Describe More Than One Table Using information_schema?

Execute the following command to describe several tables using pgAdmin:

SELECT * 
FROM information_schema.COLUMNS;

The above-given output proved that the information schema successfully described all the tables.

Example #2: How to Describe a Specific Table?

Execute the below-given query to describe a specific table using pgAdmin:

SELECT * 
FROM information_schema.COLUMNS
WHERE TABLE_NAME = 'bike_details';

The output clarified that the information schema, with the aid of the SELECT statement, successfully described all the details of the selected table.

Example #3: How to Describe Column Names of a Table?

Run the below-mentioned query to describe column names of a table in PostgreSQL:

SELECT COLUMN_NAME
FROM information_schema.COLUMNS
WHERE TABLE_NAME = 'bike_details';

The SELECT statement successfully fetched the column names with the help of the information schema.

Conclusion

To describe a Postgres table, the “\d”, “\dt”, and information_schema are used in PostgreSQL. In PostgreSQL, describing a table means checking the table’s structure or getting all the information about a table. In this write-up, we have learned how to describe single or multiple tables using some suitable examples.