Add table column comments...

Moderator: NorbertKrupa

Post Reply
User avatar
fsalvelt
Intermediate
Intermediate
Posts: 54
Joined: Sun Mar 18, 2012 1:34 am

Add table column comments...

Post by fsalvelt » Tue Apr 17, 2012 9:54 pm

Hello,

I can't figure out how to add a comment to a table column. I know there is an COMMENT ON COLUMN command but it just gives me an error.

Here is what I am seeing:

Code: Select all

vadmin=> CREATE TABLE title (title_key int);
CREATE TABLE
vadmin=> COMMENT ON TABLE title IS 'Store employee titles';
COMMENT
vadmin=> COMMENT ON COLUMN title.title_key IS 'Table PK';
ROLLBACK:  Column "title"."title_key" does not exist as a projection column.
Am I missing something?

Thanks!
Thank, Fred

User avatar
JimKnicely
Site Admin
Site Admin
Posts: 1823
Joined: Sat Jan 21, 2012 4:58 am
Contact:

Re: Add table column comments...

Post by JimKnicely » Wed Apr 18, 2012 2:01 pm

fsavalt:

You can't add comments on table columns. The comments for columns need to be on the table's projection columns.

For instance:

Code: Select all

dbadmin=> CREATE TABLE title (title_key int);
CREATE TABLE
dbadmin=> SELECT projection_name FROM projection_storage WHERE anchor_table_name = 'title';
 projection_name
-----------------
(0 rows)
There are no projections yet for the TITLE table. We can create a "super" projection by inserting a record:

Code: Select all

dbadmin=> insert into title values (1);
 OUTPUT
--------
      1
(1 row)

dbadmin=> SELECT projection_name FROM projection_storage WHERE anchor_table_name = 'title';
 projection_name
-----------------
 title_node0001
 title_node0002
 title_node0003
(3 rows)

dbadmin=> COMMENT ON COLUMN title_node0001.title_key IS 'Table PK';
COMMENT
You can query the V_CATALOG.COMMENTS system table to see the comments on all database objects. Let's check the one we just added:

Code: Select all

dbadmin=> SELECT object_type, object_name, comment FROM comments WHERE comment = 'Table PK';
 object_type |       object_name        | comment
-------------+--------------------------+----------
 COLUMN      | title_node0001.title_key | Table PK
(1 row)
As you can see, we only added a comment to the column of the projection on node 1. If your table has projections spread across nodes, you have to add the comment to each of the projections manually. Since I have 3 nodes, I can create the additional comments like so:

Code: Select all

dbadmin=> COMMENT ON COLUMN title_node0002.title_key IS 'Table PK';
COMMENT
dbadmin=> COMMENT ON COLUMN title_node0003.title_key IS 'Table PK';
COMMENT
dbadmin=> SELECT object_type, object_name, comment FROM comments WHERE comment = 'Table PK';
 object_type |       object_name        | comment
-------------+--------------------------+----------
 COLUMN      | title_node0001.title_key | Table PK
 COLUMN      | title_node0002.title_key | Table PK
 COLUMN      | title_node0003.title_key | Table PK
(3 rows)
Hope this helps!
Jim Knicely

Image

Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.

Post Reply

Return to “New to Vertica”