Redshift not inserting data properly

Question

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.

Answer 1

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