How to add a virtual column ?

I need to make something like an auto increment pk in a new table i am about to load into Vertica.
But i dont have that data into my csv file !
How can i do this ? 

Comments

  • What you need is a sequence here that will generate that auto increment pk ! 
    -make sure the sequence is not used by any so the nextval should be 1 
    See example :
    create table bla (id int PRIMARY KEY, --virtual column
    col2 ,
    col3,
    ...
    );
    --create sequence
    CREATE SEQUENCE seq_bla START 1;
    --load the data
    copy bla(
    id as seg_bla.nextval,
    col2,
    col3,
    ....
    ) from '/csv_file' delimiter ',' direct;
    This should do the trick
  • i will give it a try
    Thx
  • Hi,
    that worked , but i don't get all the data loaded into my table . i Mean i have millions of rows of data but not all appear in the count(*) from the loaded table.
  •   If you don't specify any REJECTED DATA or EXCEPTIONS log file location then  the rejected data and the exceptions will be saved into 'catalog_dir/CopyErrorLogs/tablename-filename-of-source-copy-from-rejections' by default.<BR />  Take a look at that data and you will find why not all of your data loaded. Also read the docs on copy command for more details.

  • you can also use 
    create table xyz(ID auto_increment,name varchar(2));


    xyz.txt
    an
    bn
    cn
    dn

    copy xyz from local 'path/xyz.txt';
    select * from xyz;
    1 an
    2 bn
    3 cn
    4 dn

    this may be enough

  • Thank you,solved my problem!

Leave a Comment

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