Need to use DEFAULT keyword in data file for COPY command
Posted: 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.
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:
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:
Anyone have any ideas how I could do this?
Thanks,
Beth
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)
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)
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)
Thanks,
Beth