Add table column comments...

[fsalvelt]

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!

[JimKnicely]

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!