Live Documentation in Vertica using comment on

A very important statement in Vertica is the comment on statement, this statement allows you to create a sort of extended proprieties on the objects you create in the database. Among this objects are: - you can comment on:

  • COLUMN
  • CONSTRAINT
  • FUNCTION
  • LIBRARY
  • NODE
  • PROJECTION
  • SCHEMA
  • SEQUENCE
  • TABLE
  • TRANSFORM FUNCTION
  • VIEW
The advantage of using the comment statement/extended properties is that the documentation, notes, and so on stay with the databases and can be used as a "live documentation" mechanism. They are backed up with the database, and scripted out with the build scripts. Let's go and see some examples of how we can add comments to our objects. Add comment to a schema: 
(dbadmin@:5433) [dbadmin]  dn
         List of schemas
     Name     |  Owner  | Comment
--------------+---------+---------
 v_internal   | dbadmin |
 v_catalog    | dbadmin |
 v_monitor    | dbadmin |
 public       | dbadmin |
 TxtIndex     | dbadmin |
 v_demo       | dbadmin |
 store        | dbadmin |
 online_sales | dbadmin |
 dba          | dbadmin |
(9 rows)
-- add the comment
(dbadmin@:5433) [dbadmin] * comment on schema dba is 'this schema stores views used only by dba';
-- quesry the schemas again
(dbadmin@:5433) [dbadmin]  dn
                          List of schemas
     Name     |  Owner  |                  Comment
--------------+---------+-------------------------------------------
 dba          | dbadmin | this schema stores views used only by dba
 v_internal   | dbadmin |
 v_catalog    | dbadmin |
 v_monitor    | dbadmin |
 public       | dbadmin |
 TxtIndex     | dbadmin |
 v_demo       | dbadmin |
 store        | dbadmin |
 online_sales | dbadmin |
Add comment on a table 
(dbadmin@:5433) [dbadmin] * create table tbl1(id int);
CREATE TABLE
(dbadmin@:5433) [dbadmin]  dt tbl1;
List of tables
-[ RECORD 1 ]----
Schema  | public
Name    | tbl1
Kind    | table
Owner   | dbadmin
Comment |

(dbadmin@:5433) [dbadmin] * comment on table public.tbl1 is 'Table used for staging data ';
COMMENT
(dbadmin@:5433) [dbadmin]  dt tbl1;
List of tables
-[ RECORD 1 ]-------------------------
Schema  | public
Name    | tbl1
Kind    | table
Owner   | dbadmin
Comment | Table used for staging data
Add comment on a projection 
(dbadmin@:5433) [dbadmin] * dj tbl1_super
              List of projections
 Schema |    Name    |  Owner  | Node | Comment
--------+------------+---------+------+---------
 public | tbl1_super | dbadmin |      |
(1 row)

(dbadmin@:5433) [dbadmin] * comment on projection public.tbl1_super is 'Super projection for staging data ';
COMMENT
(dbadmin@:5433) [dbadmin]  dj tbl1_super
                            List of projections
 Schema |    Name    |  Owner  | Node |              Comment
--------+------------+---------+------+------------------------------------
 public | tbl1_super | dbadmin |      | Super projection for staging data
Add comment on a column
  • you must associate the projection name and column name;
(dbadmin@:5433) [dbadmin]  comment on column tbl2_super.id is 'Primary key and also blabla bla ';
COMMENT

-- to query the column comment definition use the comments table from the v_catalog schema
(dbadmin@:5433) [dbadmin] * select * from v_catalog.comments where object_type='COLUMN' and object_name='tbl2_super.id';
-[ RECORD 1 ]------+---------------------------------
comment_id         | 45035996273920442
object_id          | 45035996273905542
object_type        | COLUMN
object_schema      | public
object_name        | tbl2_super.id
owner_id           | 45035996273704962
owner_name         | dbadmin
creation_time      | 2015-05-03 19:42:52.79828-03
last_modified_time | 2015-05-03 19:42:52.79828-03
comment            | Primary key and also blabla bla
 Also is important to know how to remove a comment from an object.
  • once the object has been removed the comments also will be removed.
To remove the comment from a schema for example: 
(dbadmin@:5433) [dbadmin] * comment on schema dba is null;
COMMENT

-- see schema meta
(dbadmin@:5433) [dbadmin] * dn dba
     List of schemas
 Name |  Owner  | Comment
------+---------+---------
 dba  | dbadmin |
 To get a full list of your comments query the v_catalog.comments table. I find this very useful as i really don't remember what is what after a few months in a project and also this statement allows me create a better bound while working with other DBA's. Of course, ultimately, comment statement/extended properties are just one of the various means of ensuring that your databases are well-documented and easily understood, such as using long descriptive object names.

Copyright 2020. All Rights Reserved