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?
Vertica copy on tables with identity columns
Moderator: NorbertKrupa
- JimKnicely
- Site Admin
- Posts: 1825
- Joined: Sat Jan 21, 2012 4:58 am
- Contact:
Re: Vertica copy on tables with identity columns
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:
IMHO, a better option is to use a sequence as a default value...
Example:
But you will have to make sure you maintain the sequence if you've inserted values beyond the sequences current value...
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)
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)
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
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
Re: Vertica copy on tables with identity columns
Hi!,
Any idea to copy a table with identity field? Because for me is no a option to change all my tables for sequences...
Any idea to copy a table with identity field? Because for me is no a option to change all my tables for sequences...
- JimKnicely
- Site Admin
- Posts: 1825
- Joined: Sat Jan 21, 2012 4:58 am
- Contact:
Re: Vertica copy on tables with identity columns
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?
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
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.