Loading CSV data with NaN into AWS Redshift

Question

I am trying to load a CSV file from AWS S3 into AWS Redshift. The CSV file contains a line like:

15,NaN,0

The table was created via:

CREATE TABLE foo (a INT, b DOUBLE PRECISION, c INT);

And I am trying to load the table using the COPY command:

COPY foo (a, b, c) FROM "s3://" CREDENTIALS ... CSV;

And I get an error complaining:

Invalid digit value 'N'

Trying to load that same line via an INSERT statement manually works just fine:

INSERT INTO foo (a, b, c) VALUES (15, 'NaN', 0);

Any help would be much appreciated!

Answer 1

You need to tell Redshift to load NaN as a NULL if that is what you want to do.

For example:

COPY foo from 's3://xxx' credentials 'xxxx' DELIMETER AS ',' NULL 'NaN';

This should execute successfully and insert a NULL into the table instead of NaN.