COPY command NULL option question...

Moderator: NorbertKrupa

Post Reply
User avatar
bud
Newbie
Newbie
Posts: 14
Joined: Fri Oct 11, 2013 1:45 am

COPY command NULL option question...

Post by bud » Mon Jan 04, 2016 6:59 pm

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)
Bud Anderson

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

Re: COPY command NULL option question...

Post by JimKnicely » Mon Jan 04, 2016 7:34 pm

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)
Jim Knicely

Image

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

User avatar
bud
Newbie
Newbie
Posts: 14
Joined: Fri Oct 11, 2013 1:45 am

Re: COPY command NULL option question...

Post by bud » Sat Jan 09, 2016 8:45 pm

Thank you Jim! This solution works for perfectly.
Bud Anderson

Post Reply

Return to “Vertica Data Load”