Vertica copy on tables with identity columns

Moderator: NorbertKrupa

Post Reply
sreediff
Newbie
Newbie
Posts: 18
Joined: Mon Mar 28, 2016 2:51 pm

Vertica copy on tables with identity columns

Post by sreediff » 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?

User avatar
JimKnicely
Site Admin
Site Admin
Posts: 1825
Joined: Sat Jan 21, 2012 4:58 am
Contact:

Re: Vertica copy on tables with identity columns

Post by JimKnicely » 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
Jim Knicely

Image

Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.

sowe
Newbie
Newbie
Posts: 11
Joined: Thu Jul 02, 2015 8:43 pm

Re: Vertica copy on tables with identity columns

Post by sowe » 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...

User avatar
JimKnicely
Site Admin
Site Admin
Posts: 1825
Joined: Sat Jan 21, 2012 4:58 am
Contact:

Re: Vertica copy on tables with identity columns

Post by JimKnicely » 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?
Jim Knicely

Image

Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.

Post Reply

Return to “General”