CODEWITHSUNDEEP
amazon-redshift
JuanCaicedo
JuanCaicedo

Reputation: 3448

Decimal: Integral number too large in Redshift COPY

I'm getting the following error from Redshift.

Decimal: Integral number too large

This is happening when inserting the following csv line

2015-03-20,A_M300X250CONTENT_INT_ADSENSE,3443,3443,1.4,13,,

The error is being thrown by 1.4.

The definition of that column is this:

schemaName | tablename | column          | type         | encoding | disktkey | sortkey | notnull 
-----------|-----------|-----------------|--------------|----------|----------|---------|---------
public     | partners  | revenue_partner | numeric(7,7) | none     | false    | 0       | false

This copy worked fine when the type was numeric(7,2), but I need to change it to fix a rounding error.

Upvotes: 2

Views: 1951

Answers (2)

mmilleruva
mmilleruva

Reputation: 2178

Reading the docs http://docs.aws.amazon.com/redshift/latest/dg/r_Numeric_types201.html

It looks like a numeric(7,7) data type can only store values between 0-1 with 7 significant figures. The second number is the number of values you can have after the decimal and the first number - the second number will be the number of values you can have before the decimal.

Upvotes: 2

Joe Harris
Joe Harris

Reputation: 14045

numeric(7,7) means the total number of digits allowed is 7 and all 7 are allocated as decimals. If you want 7 decimals and 7 digits you need numeric(14,7)

Upvotes: 3

Related Questions