Describing table in PostgreSQL

Posted on

Describing table in PostgreSQL

Describing table in PostgreSQL Database Server is quite easy somehow. Especially if it is done in PostgreSQL Command Console. This command is the equivalent command of the command exist in MySQL Database Server executed in MySQL Command Concole. The command is ‘desc table_name’. It is used specifically to print and display the structure of tables exist in a certain database. So, to be able to print and display the structure of a certain table exist in PostgreSQL Database Server, below is the command executed :

[user@hostname ~]$ psql -Uusername mydbname 
psql (x.x.xx)
Type "help" for help.

newsikdlogin=# \dt
List of relations
Schema |         Name          | Type  |  Owner
--------+----------------------+-------+----------
public | table1                | table | postgres
public | table2                | table | postgres
public | table3                | table | postgres
public | tablemenu             | table | postgres
...
(14 rows)

As we can see, there are several tables display in the list of table within the database named ‘mydbname’. By using the command ‘\dt’, every table exist in that database will be presented. Furthermore, there is a way for describing the structure of a certain table in detail. To do that, the command typed in the PostgreSQL Command Console to achieve the purpose is shown as follows :

\d table_name

For an example if the command above is executed in PostgreSQL Command Console, it can be shown as follows :

mydbname=# \d+ tablemenu
Table "public.tablemenu"
Column      |          Type          |                          Modifiers                           | Storage     | Stats target | Description
------------+------------------------+--------------------------------------------------------------+-------------+--------------+-------------
indexkey    | bigint                 | not null default nextval('appmenu1_masterkey_seq'::regclass) | plain       |              |
indexmenu   | bigint                 |                                                              | plain       |              |
code        | character varying(255) |                                                              | extended    |              |
menuname    | character varying(255) |                                                              | extended    |              |
mastermenu  | bigint                 |                                                              | plain       |              |
menuitem    | boolean                |                                                              | plain       |              |
icon        | character varying(255) |                                                              | extended    |              |
Indexes:
...
mydbname=#

The above output is generated in PostgreSQL Command Console with a few requirement or prerequisite in order for the command execution can happened. Below are the prerequisites needed for the execution of the command can be printed in PostgreSQL Command Console :

1. Check PostgreSQL Database Server is alive. Read the article titled ‘Check PostgreSQL Service Status’ to read how to do it in this link.

2. Try to access it from command line using the reference given in the article titled ‘PostgreSQL Database Access from Command Line in Linux’ which can be visited in this link and another article titled ‘Access PostgreSQL without Password’ on accessing PostgreSQL Database Server without having to provide the password interactively in this link. Another article describing on how to connect to PostgreSQL Database Server can be read in the article titled ‘Connect to PostgreSQL command line linux’ in this link.As well as the article titled ‘Connecting to PostgreSQL Database via Linux  Shell Command’ which can be seen in this link.

Leave a Reply