Redshift not inserting data properly

0
votes

I have data type as decimal(18,4), through copy command I am inserting json into redshift table. For the value 0.13 it is inserting properly but for the value 0.1475 is it inserting as 0.1474.

1
amazon-redshift
We might lose precision when loading numbers from data files in JSON format to a column that is defined as a numeric data type. Some floating point values are not represented exactly in computer systems. As a result, data you copy from a JSON file might not be rounded as you expect. To avoid a loss of precision Represent the number as a string by enclosing the value in double quotation characters,Use ROUNDEC to round the number instead of truncating and Instead of using JSON or Avro files, use CSV, character-delimited, or fixed-width text files - Srinivasu

1 Answers

2
votes

Most accurate representation of 0.1475 in IEEE754 standard for Double is

1.47499999999999992228438827624E-1 (similar but with less digits for Float)

Redshift uses (either Float or Double) internally before slotting the number into the column with defined precision (of 4 in your case) and does not apply rounding but simply trims the result to 4 decimal places.

Only values of X + Y/(2^N) will appear precise, which you can test. For 4 decimal places the best value is 1/16 = 0.0625

Therefore you have to pass ROUNDEC option when using copy as per docummentation here