How to Show Table Structure using Query in PostgreSQL Database Server

Posted on

Introduction

This article will show how to describe or to show the structure of a table in PostgreSQL Database Server. Actually, the process for showing or describing the table’s structure. There are several ways for showing or describing the table’s structure. The first one is by using the short command where the execution of it is possible in PostgreSQL command console. The other one is by executing a more complex query in the PostgreSQL command console. The first way for showing or describing the table structure exist in an article with the title of ‘How to Describe the Table in a PostgreSQL Database Server’ in this link. This article will discuss the other way to do it.

Showing and Describing the Table’s Structure using Query of PostgreSQL Database Server

Reviewing the previous article, executing the query below will describe the table’s structure :

\d+ table_name

But in some cases, there is a condition where the execution of the above query ends in an error message. The following is the example of query execution triggering an error message :

mydb=# set search_path to myschema;
SET
mydb=# \d mytable;
ERROR: column c.relhasoids does not exist
LINE 1: ...riggers, c.relrowsecurity, c.relforcerowsecurity, c.relhasoi...
^
mydb=#

In order to show the table’s structure using a different method, just use another query or command. Those query or command exist as follows :

mydb=# select table_name, column_name, data_type from information_schema.columns where table_name = 'assets';
table_name  | column_name       | data_type
------------+-------------------+-----------------------------
assets      | id                | integer
assets      | brand_type        | character varying
assets      | code              | character varying
assets      | name              | character varying
assets      | id_category       | integer
assets      | availability      | smallint
assets      | id_condition      | integer
assets      | id_brand          | integer
assets      | old_brand         | character varying
assets      | id_service_stat   | smallint
assets      | acquiring_value   | double precision
assets      | acquiring_date    | date
assets      | id_type           | integer
assets      | brand_name        | character varying
assets      | created_by        | integer
assets      | updated_by        | integer
assets      | created_at        | timestamp without time zone
assets      | updated_at        | timestamp without time zone

(18 rows)
mydb=#

As in the above command execution, the query can produce an output describing the structure of the tables.

Leave a Reply