The Vertica Database Forums

Posted: **Mon Oct 10, 2016 4:34 am**

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/Vertic ... d-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?

Posted: **Mon Oct 10, 2016 4:43 pm**

Hi,

I do not think there is a way to get the COPY command to "insert" data into an IDENTITY field.

You could exclude that column in your COPY command allowing Vertica to generate IDENTITY values for you, but I do not think that is what you want.

Example:

Code: Select all

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(> );
CREATE TABLE

dbadmin=> COPY TEST1 (CHART_TYPE_DESC, CREATED_BY_ID, MODIFIED_BY_ID) FROM STDIN;
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>> test1|jim|2016-10-10
>> \.

dbadmin=> select * from TEST1;
 TEST1_ID | CHART_TYPE_DESC | CREATED_BY_ID | MODIFIED_BY_ID
----------+-----------------+---------------+----------------
        1 | test1           | jim           | 2016-10-10
(1 row)

IMHO, a better option is to use a sequence as a default value...

Example:

Code: Select all

dbadmin=> CREATE SEQUENCE TEST1_SEQ;
CREATE SEQUENCE

dbadmin=> CREATE TABLE TEST1
dbadmin-> (
dbadmin(> TEST1_ID int DEFAULT TEST1_SEQ.NEXTVAL,
dbadmin(> CHART_TYPE_DESC varchar(100) NOT NULL,
dbadmin(> CREATED_BY_ID varchar(50),
dbadmin(> MODIFIED_BY_ID varchar(50)
dbadmin(> );
CREATE TABLE

dbadmin=> insert into test1 (CHART_TYPE_DESC, CREATED_BY_ID, MODIFIED_BY_ID) values ('test1', 'AAA', 'BBB');
 OUTPUT
--------
      1
(1 row)

dbadmin=> \! cat /home/dbadmin/jim.txt
2|test2|AAA|BBB
3|test3|CCC|DDD

dbadmin=> copy test1 from '/home/dbadmin/jim.txt';
 Rows Loaded
-------------
           2
(1 row)

dbadmin=> select * from test1;
 TEST1_ID | CHART_TYPE_DESC | CREATED_BY_ID | MODIFIED_BY_ID
----------+-----------------+---------------+----------------
        1 | test1           | AAA           | BBB
        2 | test2           | AAA           | BBB
        3 | test3           | CCC           | DDD
(3 rows)

But you will have to make sure you maintain the sequence if you've inserted values beyond the sequences current value...

Code: Select all

dbadmin=> select TEST1_SEQ.CURRVAL;
 CURRVAL
---------
       1
(1 row)

dbadmin=> select TEST1_SEQ.NEXTVAL;
 NEXTVAL
---------
       2
(1 row)

dbadmin=> select TEST1_SEQ.NEXTVAL;
 NEXTVAL
---------
       3
(1 row)

dbadmin=> commit;
COMMIT

Posted: **Wed Mar 08, 2017 10:48 pm**

Hi!,
Any idea to copy a table with identity field? Because for me is no a option to change all my tables for sequences...

Posted: **Fri Mar 10, 2017 3:02 pm**

Sowe,

What are you trying to do? Do you want to copy data from a table that has an identity to a column that has an identity?

The Vertica Database Forums

Vertica copy on tables with identity columns

Vertica copy on tables with identity columns

Re: Vertica copy on tables with identity columns

Re: Vertica copy on tables with identity columns

Re: Vertica copy on tables with identity columns