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!

Accepted Answer

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.

Loading CSV data with NaN into AWS Redshift

Solution

Problem