handling numeric null values using copy command in amazon redshift

Question

while trying to load data to redshift using s3 I am getting an error

Invalid digit, Value 'N', Pos 0, Type: Decimal

its failing while trying to load NULL value to a numeric datatype column (column is nullable)

although i am using NULL AS '\000' conversion.

COPY campaign
FROM 's3://test/campaign.csv' 
credentials '------------------' 
EMPTYASNULL
NULL AS '\000' 
delimiter ',' 
region '----';

how can we handle such scenarios?

Table:

CREATE TABLE campaign ( 
  name VARCHAR(255) SORTKEY NOT NULL, 
  discount_med DECIMAL(5,2), 
  discount_packages DECIMAL(5,2), 
  discount_test DECIMAL(5,2) 
);

Sample input:

test1,5.25,NULL,1

Answer 1

Instead of NULL AS '\000' use NULL as 'NULL'. This worked for me on your table and data:

COPY campaign
FROM 's3://denis-stackoverflow/campaign.csv'
credentials '---'
NULL AS 'NULL'
EMPTYASNULL
delimiter ','
region 'ap-southeast-2';