How to import fields (such as IDS) with AUTO_INCREMENT constraints from other databases to Vertic

If you create a table with the AUTO_INCREMENT constraint field (ID) in the Vertica database, due to the limitations of the AUTO_INCREMENT constraint, the COPY operation will fail due to conflicts during data import. After analysis and test, create a SEQUENCE, create a table, and set the default value of the ID field to NEXTVAL (schemaname. sequencenam

If you create a table with the AUTO_INCREMENT constraint field (ID) in the Vertica database, due to the limitations of the AUTO_INCREMENT constraint, the COPY operation will fail due to conflicts during data import. After analysis and test, create a SEQUENCE, create a table, and set the default value of the ID field to NEXTVAL ('schemaname. sequencenam

If you create a table with the AUTO_INCREMENT constraint field (ID) in the Vertica database, due to the limitations of the AUTO_INCREMENT constraint, the COPY operation will fail due to conflicts during data import.

After analysis and test, you can create a SEQUENCE, create a table, and set the default value of the ID field to NEXTVAL ('schemaname. sequencename') to COPY the data of the AUTO_INCREMENT constraint field.The procedure is as follows:

1. Create a SEQUENCE:

Create SEQUENCE public. mytab_id_seq;

2. Create a table:
Create table if not exists public. mytab (
Id bigint not null default nextval ('Public. mytab_id_seq ') unique primary key,
...
);

3. import data:
Copy public. mytab (we_id,...) from '/home/username/mytab.csv' DELIMITER '| ';

4. Maximum query ID:

Select max (id) from mytab;

5. Modify the initial value of SEQUENCE to the maximum value of ID (such as 720 ):

Alter sequence public. mytab_id_seq restart with 720;