CODEWITHSUNDEEP
databasecsvvertica
CompEng
CompEng

Reputation: 7376

How can I import data from .csv to a table which has auto increment column?

I want to import data from csv to a table which has auto increment column. But it gives error because the id column is auto increment it does not accept. How can I import data to that table?

my table has the columns id and name

and my .csv file is 

4   jack
10  carl

Upvotes: 1

Views: 1414

Answers (1)

Guillaume
Guillaume

Reputation: 2879

The trick is to use a sequence instead of an identity here. The docs describes the differences between sequence, auto_increment and identity.

What is of interest to you, is that you can indeed force a value of a column with a sequence. Of course, be careful yourself to not have duplicate and to set the next value of the sequence afterwards to make sure you will avoid future duplicates.

The simplest example is:

CREATE TABLE test (id INT, value VARCHAR(10));
CREATE SEQUENCE seqtest;
ALTER TABLE test ALTER COLUMN id set default NEXTVAL('seqtest');

You can then see that it does what you expect:

INSERT INTO test (value) VALUES ('default');
INSERT INTO test (id, value) VALUES (42, 'forced');
select * from test;
 id |  value
----+---------
  1 | default
 42 | forced

And to be safe afterwards:

ALTER SEQUENCE seqtest RESTART WITH 43;

Upvotes: 2

Related Questions