Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

How to push IDs (AUTO_INCREMENT) from another DB into Vertica?


Right now I need to let Vertica insert new IDs (auto_increment) and then remap my tables just because I can not insert the existent IDs from another DB (let's say MySQL).

I would like to be able to do something like this:

create table test( id int, txt varchar(3), primary key (id) ); 
INSERT INTO test(id, txt) VALUES(1, 'foo');
INSERT INTO test(id, txt) VALUES(2, 'bar');
INSERT INTO test(id, txt) VALUES(3, 'lol');
alter table test alter column id AUTO_INCREMENT;

How can I export data from my old Database (not Vertica) preserving the IDs?

thank you


  • Hi, 

    You can create a sequence starting from the last ID inserted and start use it when doing the insert.

    For example,

    dbadmin=> create sequence my_seq START 100;


    dbadmin=> INSERT INTO test(id, txt) VALUES(nextval('my_seq'), 'foo');




    (1 row)

    dbadmin=> select * from test;

     id  | txt 


       1 | foo

     100 | foo

       2 | bar

       3 | lol

    (4 rows)

    Hope this helps, 


Leave a Comment

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

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.