CODEWITHSUNDEEP
amazon-s3amazon-redshift
Kumar Pushkar
Kumar Pushkar

Reputation: 103

handling numeric null values using copy command in amazon redshift

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

Upvotes: 10

Views: 16328

Answers (1)

denismo
denismo

Reputation: 800

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';

Upvotes: 13

Related Questions