How to retrieve the COMMENT for a Database object

Products

• VMware Tanzu Greenplum

Issue/Introduction

In GPDB, comment stores a comment about a database object. These can be for tables, schemas, databases, etc.

Further details about this feature and about how to add a comment to an object is available in the Greenplum Guide.

At times you may need to retrieve comments for the database objects. The following article will detail a few different ways that this can be done.

Environment

 

Resolution

For a Table

To get the comments for a table there are 2 methods:
 

Options 1:

Use the psql meta-command \dd pivtest1. The description column holds the COMMENT value.

 

                Object descriptions
 Schema |   Name   | Object |     Description      
--------+----------+--------+----------------------
 public | pivtest1 | table  | This is a test table

Option 2:

Get the table oid from pg_class for the table:

 

select oid,relname from pg_class where relname='pivtest1';

  oid  | relname  
-------+----------
 16994 | pivtest1

 

Using the oid for the table, run the following command:

 

select pg_catalog.obj_description(16994, 'pg_class');

obj_description    
----------------------
 This is a test table

For a Schema
 

Option 1:

Use the psql meta-commands "\dn+ pivdb". The column Description hold the COMMENT value.

 

                       List of schemas
 Name  |  Owner  | Access privileges |      Description      
-------+---------+-------------------+-----------------------
 pivdb | gpadmin |                   | this is a test schema

Option 2:

Get the schema oid from pg_namespace:

 

select oid,nspname from pg_namespace where nspname='pivdb';

  oid  | nspname 
-------+---------
 17008 | pivdb

Using the oid for the schema, run the following command:

 

select pg_catalog.obj_description(17008, 'pg_namespace');

 obj_description    
-----------------------
 this is a test schema