COPY and NULL and PAIN

craic90

Hey,

I'm trying to figure out how I can craft a query that will pull in values from a CSV file that are stored as "null".

I have a query that looks something like this:

COPY (my,list,of,columns) FROM '/a/local/file' DELIMITER ',' NULL NEED HELP HERE REJECTED DATA '/log/the/rejects' EXCEPTIONS '/log/the/exceptions' ENFORCELENGTH DIRECT NO COMMIT

Note the bit between the asterix ** where I'm not sure how to define that the CSV values for NULL are stored as String literals. When I attempt to run the query, the lines with "null" are stored in the rejects file.

Any help much appreciated!

swalkaus

Does the following example from the documentation help?
=> COPY public.customer_dimension (customer_since FORMAT 'YYYY')
FROM STDIN
DELIMITER ','
NULL AS 'null'
ENCLOSED BY '"';

https://my.vertica.com/docs/8.0.x/HTML/index.htm#Authoring/SQLReferenceManual/Statements/COPY/Examples.htm