How to Describe the Table in a PostgreSQL Database Server

Posted on

How to describe the table in a PostgreSQL Database Server will be the main idea in this article. It is about how to get the information about the available structure which consists of several columns or fields in other to be further manipulated in a query language. The following is the steps taken in order to be able to describe the table :

1. Just login to the PostgreSQL Database Server.

In order to describe a certain table, the first step in fact is to be able to login to PostgreSQL Database Server. Below is the login process shown :

root@hostname:~# psql -Uusername databasename
psql (9.6.9, server 10.5)
WARNING: psql major version 9.6, server major version 10.
Some psql features might not work.
Type "help" for help.

databasename=#

2. Try to enlist the table exists in the Database Server.

After successfully logged in to the PostgreSQL Database Serve, just type the following to generate an output of table exists in the PostgreSQL Database Server as shown below :

databasename=# \d+
                 List of relations
 Schema |           Name           | Type  | Owner 
--------+--------------------------+-------+-------
 ...
 public | users                    | table | pgsql
(17 rows)

databasename=# 

3. As shown in the above output, there is list of tables exist in the database. So, the main goal is just to execute the command for describing the table exist in the table. The description of the table in this article is ‘users’ for an example taken. Just execute the following command in order to do that as shown below :

databasename=# \d+ users                                                                                                                                                                                                                 
                                                        Table "public.users"                                                                                                                                                      
  Column  |          Type          |                        Modifiers                        | Storage  | Stats target | Description                                                                                              
----------+------------------------+---------------------------------------------------------+----------+--------------+-------------                                                                                             
 user_id  | integer                | not null default nextval('users_user_id_seq'::regclass) | plain    |              |                                                                                                          
 username | character varying(255) |                                                         | extended |              |                                                                                                          
 password | character varying(255) |                                                         | extended |              |                                                                                                          
Indexes:                                                                                                                                                                                                                          
    "users_pkey" PRIMARY KEY, btree (user_id)                                                                                                                                                                                     
                                                                                                                                                                                                                                  
databasename=#

So, the main pattern for describing the table named ‘users’ in a database named ‘databasename’ as an example in this article can be viewed as follows :

\d+ table_name

Where the table_name in the context of this article as an example is ‘users’.

Leave a Reply