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.
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
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?