Vertica copy command with identity column
sreeblr
- Select Field - Employee
i created two tables
CREATE TABLE TEST1
(
TEST1_ID identity(1) ,
CHART_TYPE_DESC varchar(100) NOT NULL,
CREATED_BY_ID varchar(50),
MODIFIED_BY_ID varchar(50)
);
CREATE TABLE TEST2
(
TEST2_ID int ,
CHART_TYPE_DESC varchar(100) NOT NULL,
CREATED_BY_ID varchar(50),
MODIFIED_BY_ID varchar(50)
);
When i insert into table TEST1 doesnt work
insert into table test1 values (1,'ABC','A','A');
however taking inputs from
i added SELECT set_optimizer_directives('AllowIdentityInsert=True');
and the insert worked.
However when i tried with copy command it doesnt work and gives error
vsql -U dbadmin -w test -c "SELECT set_optimizer_directives('AllowIdentityInsert=True');COPY test3 FROM '/home/dbadmin/test.csv' delimiter E',' EXCEPTIONS '/home/dbadmin/exceptions';"
COPY: Input record 1 has been rejected (Too many columns found). Please see /home/dbadmin/test/v_test_node0001_catalog/CopyErrorLogs/test3-test.csv-copy-from-rejected-data, record 1 for the rejected record.
however when i insert into table test2 which doesnt have identity column copy command works.
Is there any way to ask copy to ignore identity column and allow insert?
Comments
This is crucial. When importing data from another DBMS, you are more or less obliged to use explicit named sequences instead of identity columns, so you loose all the benefit of the identity columns (you have no last_insert_id(), you have to declare sequences as independent objects, ...). There should definitely be a parameter allowing one to set the values of identity columns.
The reason why it has been implemented this way, something along the lines of people tending to loose confidence in systems where such overwriting of identity columns are possible, is fallacious: you just avoid using identity columns because you want to use the IDs already present in your exports. And there is a plethora of use cases where this limitation is wrong, as long as plenty of other ways to corrupt your data if you really want to.
In brief, it's a real showstopper.
Plus, we know that it's not a low level constraint, but just a design choice, since Vertica is able to copy identity values from one node to another using imports/exports.
Dear Vertica, do something!
@sreeblr - You can load the data into TEST2, connect to the same DB and run a COPY FROM VERTICA to get data int TEST1...
Example:
dbadmin=> CREATE TABLE TEST1 dbadmin-> ( dbadmin(> TEST1_ID identity(1) , dbadmin(> CHART_TYPE_DESC varchar(100) NOT NULL, dbadmin(> CREATED_BY_ID varchar(50), dbadmin(> MODIFIED_BY_ID varchar(50) dbadmin(> dbadmin(> ); CREATE TABLE dbadmin=> CREATE TABLE TEST2 dbadmin-> ( dbadmin(> TEST2_ID int , dbadmin(> CHART_TYPE_DESC varchar(100) NOT NULL, dbadmin(> CREATED_BY_ID varchar(50), dbadmin(> MODIFIED_BY_ID varchar(50) dbadmin(> dbadmin(> ); CREATE TABLE dbadmin=> \! cat /home/dbadmin/test.csv 1,A,B,C 10,D,E,F dbadmin=> COPY test2 FROM '/home/dbadmin/test.csv' DELIMITER ',' DIRECT; Rows Loaded ------------- 2 (1 row) dbadmin=> SELECT * FROM test2; TEST2_ID | CHART_TYPE_DESC | CREATED_BY_ID | MODIFIED_BY_ID ----------+-----------------+---------------+---------------- 1 | A | B | C 10 | D | E | F (2 rows) dbadmin=> SELECT get_config_parameter('CopyFromVerticaWithIdentity'); get_config_parameter ---------------------- 1 (1 row) dbadmin=> CONNECT TO VERTICA test_db USER dbadmin PASSWORD 'xxxxxxx' ON 'my_host', 5433; CONNECT dbadmin=> COPY test1 FROM VERTICA test_db.test2 DIRECT; Rows Loaded ------------- 2 (1 row) dbadmin=> DISCONNECT test_db; DISCONNECT dbadmin=> SELECT * FROM test1; TEST1_ID | CHART_TYPE_DESC | CREATED_BY_ID | MODIFIED_BY_ID ----------+-----------------+---------------+---------------- 1 | A | B | C 10 | D | E | F (2 rows)Then alter the Identity's sequence to restart with the max value for TEST1_ID + 1. Now you can use the identity to insert new values ...
dbadmin=> SELECT sequence_name, current_value FROM sequences WHERE identity_table_name = 'TEST1'; sequence_name | current_value --------------------+--------------- TEST1_TEST1_ID_seq | 0 (1 row) dbadmin=> ALTER SEQUENCE TEST1_TEST1_ID_seq RESTART WITH 11; ALTER SEQUENCE dbadmin=> INSERT INTO test1 (CHART_TYPE_DESC, CREATED_BY_ID, MODIFIED_BY_ID) SELECT 'G', 'H', 'I'; OUTPUT -------- 1 (1 row) dbadmin=> SELECT * FROM test1; TEST1_ID | CHART_TYPE_DESC | CREATED_BY_ID | MODIFIED_BY_ID ----------+-----------------+---------------+---------------- 1 | A | B | C 10 | D | E | F 11 | G | H | I (3 rows)