Need to use DEFAULT keyword in data file for COPY command

Moderator: NorbertKrupa

Post Reply
beth
Intermediate
Intermediate
Posts: 58
Joined: Tue Oct 01, 2013 12:42 pm

Need to use DEFAULT keyword in data file for COPY command

Post by beth » Thu Aug 21, 2014 7:56 pm

Hi all!

I'm stuck on what I think should be a simple thing. I want to load data into a table where the pk of that table has a default value which is a sequence.

Code: Select all

dbadmin=> create sequence public.beth_seq;
CREATE SEQUENCE

dbadmin=> create table public.beth (x int default public.beth_seq.nextval, y varchar(10));
CREATE TABLE

dbadmin=> \! cat /home/dbadmin/beth.txt
test

dbadmin=> copy public.beth from '/home/dbadmin/beth.txt';
 Rows Loaded
-------------
           0
(1 row)
The copy fails because it is trying to insert the text into the int column X. My problem is, I can't change the COPY command as it is coming from an ETL tool. I know we have the DEFAULT keyword that I can use like this:

Code: Select all

dbadmin=> insert into public.beth values (default, 'Test');
 OUTPUT
--------
      1
(1 row)

dbadmin=> select * from public.beth;
 x |  y
---+------
 1 | Test
(1 row)
Is there a way I can include the DEFAULT keyword in the data file that I am using in the COPY command? I tried this, without success:

Code: Select all

dbadmin=> \! cat /home/dbadmin/beth.txt
\default|test
dbadmin=> copy public.beth from '/home/dbadmin/beth.txt';
 Rows Loaded
-------------
           0
(1 row)
Anyone have any ideas how I could do this?

Thanks,
Beth

NorbertKrupa
GURU
GURU
Posts: 527
Joined: Tue Oct 22, 2013 9:36 pm
Location: Chicago, IL
Contact:

Re: Need to use DEFAULT keyword in data file for COPY comman

Post by NorbertKrupa » Thu Aug 21, 2014 8:55 pm

Probably a less desirable option would be to stage it then INSERT it into your production table if you can't change the COPY statement.
Checkout vertica.tips for more Vertica resources.

id10t
GURU
GURU
Posts: 732
Joined: Mon Apr 16, 2012 2:44 pm

Re: Need to use DEFAULT keyword in data file for COPY comman

Post by id10t » Fri Aug 22, 2014 1:12 pm

Hi!

[DELETED]
Last edited by id10t on Wed May 06, 2015 4:46 pm, edited 1 time in total.

beth
Intermediate
Intermediate
Posts: 58
Joined: Tue Oct 01, 2013 12:42 pm

Re: Need to use DEFAULT keyword in data file for COPY comman

Post by beth » Fri Aug 22, 2014 2:52 pm

Thanks for the responses, guys.

sKwa, I know that we need to specify the column in the COPY, but like I said, I can't change the COPY command as it's generated by a bulk load component in Talend.

I can make a change to the Talend job and select the sequence value prior to the copy source data being created which will work.

Thanks, again!

NorbertKrupa
GURU
GURU
Posts: 527
Joined: Tue Oct 22, 2013 9:36 pm
Location: Chicago, IL
Contact:

Re: Need to use DEFAULT keyword in data file for COPY comman

Post by NorbertKrupa » Fri Aug 22, 2014 5:20 pm

beth wrote:I can make a change to the Talend job and select the sequence value prior to the copy source data being created which will work.
Interesting solution. Thanks for sharing!
Checkout vertica.tips for more Vertica resources.

Post Reply

Return to “Vertica Data Load”