We're Moving!

The Vertica Forum is moving to a new OpenText Analytics Database (Vertica) Community.

Join us there to post discussion topics, learn about

product releases, share tips, access the blog, and much more.

Create My New Community Account Now


Vertica try to create Comment — Vertica Forum

Vertica try to create Comment

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?

Comments

  • Jim_KnicelyJim_Knicely - Select Field - Administrator

    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)
    
  • Jim, thank you. I understood the power of love Vertica to projection :smile:

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file