Add sequence numbers to a bulk load in vertica



I am loading a huge csv to vertica using COPY command and I would like to include a sequence number to each record without modifying the csv file. I did not see anything in the documentation, but was wondering whether anyone has any idea !




  • Options

    Try setting up a column as an auto increment/Identitiy column.

    Second option is to create and setup a sequence as a default on the column you want. 

    Load your data using copy statement and specify your columns explicitly.


    Be careful with the cache setting with sequences. Do NOT set it to a low value. Your bulk loads will slow down. 


  • Options

    Thanks Kaurora, you were to the point !


    For the sake of others, I am listing explicitly what I did :


    # Create sequence

    create sequence name_seq NO MINVALUE;


    # Bulk load along with sequence number.

    COPY phlx.ob_snaps2 (seq  as name_seq.nextval, <COLUMN NAMES>) FROM LOCAL 'C:\Users\buthup\Documents\PHLX_Line3_20160624_OrderBookState.csv' DELIMITER ',' SKIP 1 ABORT ON ERROR TRAILING NULLCOLS;


    # Drop sequence.

    drop sequence name_seq;

Leave a Comment

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