Vertica copy command with identity column

sreeblrsreeblr Employee, Registered User

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

https://community.dev.hpe.com/t5/Vertica-Forum/Insert-statement-for-auto-increment-column/td-p/221757

 

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

  • arkanoviczarkanovicz Registered User

    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!

  • Jim_KnicelyJim_Knicely Administrator, Moderator, Employee, Registered User, VerticaExpert
    edited November 19

    @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)
    

Leave a Comment

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