The Vertica Forum recently got a makeover! Let us know what you think by filling out this short, anonymous survey.
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?
Hi,
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).
but!
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
Wils
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).
but!
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
Wils
0
Comments
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;
CREATE SEQUENCE
dbadmin=> INSERT INTO test(id, txt) VALUES(nextval('my_seq'), 'foo');
OUTPUT
--------
1
(1 row)
dbadmin=> select * from test;
id | txt
-----+-----
1 | foo
100 | foo
2 | bar
3 | lol
(4 rows)
Hope this helps,
Eugenia