PostgreSQL database stores data in tables. The prerequisite of storing the data in a table is the existence of the table. This signifies that we need to create a table to store data in it. In this post, we will discover the methods to describe a Postgres table created in a specific database. Describing a table means getting all the information and the structure of a table. The common methods used are:
This write-up will elaborate on these approaches one by one.
Method 1: Describe a Table Using psql
We can get the details of the table in Postgres using the psql by following the below-given steps:
Step 1: Switch the Database
To describe a Postgres table using the psql, you will first have to connect to the database in which the desired table was created. By default, when you open the psql and enter your credentials, you will be connected to the default “Postgres” database. To switch to your specific database you need to write and execute the following meta-command.
\c dbName
In the above meta-command, you will have to specify the database’s name to which you want to switch or in which the table is created. In my case, I want to describe the “messages” table which is present in “test_database”. The following meta-command will be executed in this case.
\c test_database
Executing this command will switch the database from the default “Postgres” database to the “test_database”
We are now connected to the “test_database” database.
Step 2: Get Table Description
Now we can get the description of the Postgres tables present in the connected database by running the “\dt” meta-command.
\dt
This meta-command will return the description of all the tables present in the database like this:
All the tables and their limited details are enlisted by executing the “\dt” command. To get a more detailed/precise description of the tables, execute the “\dt” meta-command with the “+” symbol.
\dt+
We can notice that this meta-command has added more details to the table’s description.
Describe a Specific Table Using psql
We can also get the description of a specific table in PostgreSQL using psql by specifying the table’s name with the “\d” meta-command. In this case, it is:
\d messages
The output is given as:
We got the description of the “messages” table using the psql. We will now learn about the second method to get the description of a Postgres table.
Method 2: Describe a Table Using pgAdmin
Now to get the table’s description using the pgAdmin, we can follow two approaches. These approaches are:
- Approach 1: Describe a Table in pgAdmin Using PostgreSQL Queries
- Approach 2: Describe a Table in pgAdmin Manually
Let’s discuss both of them one by one.
Approach 1: Describe a table in pgAdmin Using PostgreSQL Queries
The user can also get a table’s description by executing a query in the pgAdmin. The query uses the “information_schema”. The information schema is common to all the databases which contain information about the objects present in the database. To get the description you will first have to open the query tool in the pgAdmin. This can be done by right-clicking on the database’s name and selecting “Query tool” from the drop-down menu.
Now, there are two cases. We can get the description of a specific table or we can also get the description of all the tables. Let’s see both of these cases.
Case 1: Describe More Than One Table in pgAdmin
The basic structure to get the description of a table can be written as:
SELECT col1, col2, ..., colN
FROM information_schema.COLUMNS;
In our case, this query will be customized as:
SELECT *
FROM information_schema.COLUMNS;
This query will return information about all the tables in the “test_database”
The query has successfully given the description of all the tables in the current database; we can see it if we scroll down the whole output. Now we’ll get the description of our “messages” table.
Case 2: Describe a Specific Table in pgAdmin
We can get the description of a specific table by specifying the table’s name as a condition with the WHERE clause. The syntax can be written as:
SELECT col1, col2, ..., colN
FROM information_schema.COLUMNS
WHERE cond;
If we wish to get the description of the “messages” table, we will customize the query as follows.
SELECT *
FROM information_schema.COLUMNS
WHERE TABLE_NAME = 'messages';
The WHERE clause is employed to define a condition. In this case, only that data will be returned from the whole data, which fulfills the condition. The above-provided query will return the details for the “messages” table.
This is how we can get the description of a specific table.
Approach 2: Describe a Table in pgAdmin Manually
We can also manually get the table description. For that, follow the below-given steps.
Step 1: Go to the Specific Table in the Database
Let’s suppose we want to get the description of the “messages” table in “test_database”. We will first go to the “test_database” option in the side panel of the pgAdmin and expand it. Next, expand the schema and then the “public” option. Then lastly expand the table option and find the desired table, i.e., “messages” in our case.
Step 2: Get the Description of the Table
Now right-click on the “messages” option to pop a drop-down menu. Select the “Properties…” option from the menu.
By clicking on the “properties…” option, a new window will appear containing different tabs for properties of the table like this.
Following the above-mentioned steps has illustrated the description of the “messages” table. We can see more details/descriptions by moving to different tabs. For example, we can get the columns of the table by moving to the “columns” tab.
We can also see constraint details like primary, foreign, and unique keys for the table from the “Constraints” tab.
That’s all about describing single or multiple Postgres tables using different approaches.
Conclusion
We can get the description of a table using the psql and pgAdmin. In psql, we can execute the “\dt”, “\dt+” or the “\d tab_name” meta-commands to get the table’s description. In pgAdmin, we can follow the two approaches; the 1st is by using the query for information_schema and the 2nd is the manual method to get the table’s description. The Postgres query for information_schema can also be executed in psql. These methods are extensively elaborated in the above sections with reasonable implementation.
TUTORIALS ON LINUX, PROGRAMMING & TECHNOLOGY
