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:
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:
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:
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”:
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.
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.