-1

I have a TSV file like this, except with many more columns:

"name"\t"color"\t"lucky_number"
"Nick"\t"Red"\t""
""\t"Blue"\t"8"

I'm trying to COPY this file into a table and have empty cells (i.e. "") be interpreted as NULLs, which fits the semantics I'm looking for.

I can't seem to get Vertica (7.2) to do this simple thing without having to repeat a lot of code for each column I'm loading.

For example:

COPY some_table (
    name,
    color,
    lucky_number
)
FROM LOCAL file
DELIMITER AS E'\t'
ENCLOSED BY '"'
SKIP 1;

This loads empty strings, which isn't what I want, and which breaks for non-string columns like lucky_number.

So I look through the options mentioned in the docs and try out NULL AS '""', but this yields the following error message:

ERROR 2735: COPY ENCLOSING CHARACTER must not appear in the NULL specification

Searching around a bit more, it seems the suggested way to handle this is to special case the empty string for every column you are loading. (!)

COPY some_table (
    name AS CASE WHEN name_raw = '' THEN NULL ELSE name_raw,
    name_raw FILLER VARCHAR,
    color AS CASE WHEN color_raw = '' THEN NULL ELSE color_raw,
    color_raw FILLER VARCHAR,
    lucky_number AS CASE WHEN lucky_number_raw = '' THEN NULL ELSE TO_NUMBER(lucky_number_raw),
    lucky_number_raw FILLER VARCHAR
)
FROM LOCAL file
DELIMITER AS E'\t'
ENCLOSED BY '"'
SKIP 1;

Is there no better way to do this?

  • @kermit to the rescue? – Philᵀᴹ Feb 2 '16 at 22:13
  • Gosh I could be completely off base here but I recall having a similar issue 5 years ago. Do you think it has anything to do with ANSI/UTF types? I'm not able to look up the control character difference and only remember the new line ones. I recall having something similar with ANSI NULL setting in SQL Server Sorry I couldn't be more help, I'm mobile at the moment. – Ali Razeghi Feb 28 '16 at 22:22
  • @AliRazeghi - Could you elaborate? I'm not sure what you mean. – Nick Chammas Mar 1 '16 at 1:55
  • Not sure why this question was downvoted. Am happy to clarify anything that's not clear. – Nick Chammas Jul 26 '16 at 16:43
0

I do believe is the delimiter E'\t' , try to replace it.

It worked for me. i made the file a csv by using the command bellow.

sed -i 's/\\t/|/g' file 

Here is an example:

  • Copy the initial you did it

copy public.bla FROM LOCAL '/tmp/bla' DELIMITER '|' ENCLOSED BY '"' SKIP 1 ; Rows Loaded 0

(1 row)

- Load using the | instead of \t :

-- replace the delimiter
    sed -i 's/\\t/|/g' /tmp/bla 

-- see content
    cat /tmp/bla
    "name"|"color"|"lucky_number"
    "Nick"|"Red"|""
    ""|"Blue"|"8"

-- run copy with new delimiter   
    copy public.bla FROM LOCAL '/tmp/bla' DELIMITER '|' ENCLOSED BY '"' SKIP 1 ;";

-- see if data was inserted
            dbadmin=> select * from bla;
             name | color | lucky_number
            ------+-------+--------------
                  | Blue  | 8
             Nick | Red   |

If any can format this post , please do so !!!!!! why does it have to be so hard this code formatting :( !!!!

dbadmin=> SELECT ISNULL(name, 'NULL') FROM bla;
 ISNULL
--------

 Nick

Hi, so if you wanna put in null values where you got empty strings use the bellow copy command to load your data:

copy some_table(
v1 filler varchar(20) ,
v2 filler varchar(20) ,
v3 filler varchar(20) ,
name as  case when v1='' THEN 'NUUL' ELSE v1 END,
color as  case when v2='' THEN 'NULL' ELSE v2 END,
lucky_number as  case when v3='' THEN 'NULL' ELSE v3 END

)FROM '/tmp/some_table.csv' DELIMITER '|' ENCLOSED BY '"' SKIP 1 ;
  • i hope this makes sense :)
  • Are you saying if I replace the tabs with pipes, then Vertica will automatically interpret the empty strings (i.e. "") as NULLs? Because that's not what I'm seeing. – Nick Chammas Feb 8 '16 at 20:09
  • See my edited post ! – Up_One Feb 8 '16 at 22:29
  • Are those blank values in the table NULLs or empty strings? What do you get for SELECT ISNULL(name, 'NULL') FROM bla;? – Nick Chammas Feb 8 '16 at 22:33
  • Right, what your latest edit shows is that Vertica has loaded empty strings, which isn't what I'm looking for. I want Vertica to load them as NULL values. From my question: "I'm trying to COPY this file into a table and have empty cells (i.e. "") be interpreted as NULLs, which fits the semantics I'm looking for." – Nick Chammas Feb 8 '16 at 23:07
  • Regarding your latest edit, I specifically called out this approach (special casing the empty string for each column) at the end of my question. I'm looking for a better way. :) – Nick Chammas Mar 1 '16 at 1:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.