Page 1 of 1

COPY command NULL option question...

Posted: Mon Jan 04, 2016 6:59 pm
by bud
Hi,

I have csv file that has the following data:

"1","\\N","b"
"2","a","\\N"

I’m using the following copy command to load this CSV file into Vertica. How can I make the \\N value be NULL when the data is inserted into Vertica. When I try, the data is created as \N:

Code: Select all

dbadmin=> \! cat /home/dbadmin/test1.csv
"1","\\N","b"
"2","a","\\N"

dbadmin=> copy test1 from '/home/dbadmin/test1.csv' delimiter ',' null '\\N' enclosed '"';
 Rows Loaded
-------------
           2
(1 row)

dbadmin=> select * from test1;
 c1 | c2 | c3
----+----+----
  1 | \N | b
  2 | a  | \N
(2 rows)

Re: COPY command NULL option question...

Posted: Mon Jan 04, 2016 7:34 pm
by JimKnicely
Enclosing the null sequence does make it act like a regular string.

You can use the FILLER parameter of the COPY command in conjunction with a CASE statement to load the data.

Example:

Code: Select all

dbadmin=> copy test1(c1, c2f filler varchar, c3f filler varchar, c2 as case when c2f = '\N' then NULL else c2f end, c3 as case when c3f = '\N' then NULL else c3f end) from '/home/dbadmin/test1.csv' delimiter ',' enclosed by '"';
 Rows Loaded
-------------
           2
(1 row)

dbadmin=> select * from test1;
 c1 | c2 | c3
----+----+----
  1 |    | b
  2 | a  |
(2 rows)

Re: COPY command NULL option question...

Posted: Sat Jan 09, 2016 8:45 pm
by bud
Thank you Jim! This solution works for perfectly.