Vertica try to create Comment

drno-reg

Hello,

I have DB Vertica, create Schema UM and table um_users

create table um.um_users
(
id number,
first_name varchar2(50) not null,
last_name varchar2(50) not null,
password varchar2(50) not null,
last_login date,
username varchar2(50) not null,
role varchar2(50)
)
;

after I try create comment

comment on column um.um_users.first_name is 'Second name';

and get error

SQL Error [2639] [42703]: [Vertica]VJDBC ROLLBACK: Column "um_users"."first_name" does not exist as a projection column
[Vertica]VJDBC ROLLBACK: Column "um_users"."first_name" does not exist as a projection column
com.vertica.util.ServerException: [Vertica]VJDBC ROLLBACK: Column "um_users"."first_name" does not exist as a projection column

How to solve this problem?

Jim_Knicely

Column comments are on the projection columns. Note that when you create a table like you did (no segmentation or ordering specified) Vertica does not create a projection until you insert data.

dbadmin=> create schema um;
CREATE SCHEMA

dbadmin=> create table um.um_users
dbadmin-> (
dbadmin(> id number,
dbadmin(> first_name varchar2(50) not null,
dbadmin(> last_name varchar2(50) not null,
dbadmin(> password varchar2(50) not null,
dbadmin(> last_login date,
dbadmin(> username varchar2(50) not null,
dbadmin(> role varchar2(50)
dbadmin(> )
dbadmin-> ;

CREATE TABLE

dbadmin=> comment on column um.um_users.first_name is 'Second name';
ROLLBACK 2639:  Column "um_users"."first_name" does not exist as a projection column

dbadmin=> select projection_name from projections where anchor_table_name = 'um_users';
 projection_name
-----------------
(0 rows)

dbadmin=> insert into um.um_users select 1, 'Jim', 'Vertica', 'XXX', sysdate, 'coolio', 'Vertica Cheerleader';
 OUTPUT
--------
      1
(1 row)

dbadmin=> select projection_name from projections where anchor_table_name = 'um_users';
 projection_name
-----------------
 um_users_super
(1 row)

Now I can add a comment on the projection column:

dbadmin=> comment on column um.um_users_super.first_name is 'Second name';
COMMENT

dbadmin=> select comment from comments where object_name = 'um_users_super.first_name';
   comment
-------------
 Second name
(1 row)

drno-reg

Jim, thank you. I understood the power of love Vertica to projection