CODEWITHSUNDEEP
amazon-redshift
Srinivasu
Srinivasu

Reputation: 1235

Redshift not inserting data properly

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.

Upvotes: 4

Views: 1452

Answers (1)

diginoise
diginoise

Reputation: 7620

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

Upvotes: 4

Related Questions