Redshift ROUND function doesn't round in some cases?

Question

I can find a workaround, but it is really annoying and I may certainly be missing something. Redshift's ROUND function doesn't round to the number of decimals specified.

For example,

select round(cast(176 as float)/cast(492 as float),4) as result;

Above select statement will return 0.35769999999999996.

However, this statement:

select round(cast(229 as float)/cast(491 as float),4) as result;

... will return 0.4664.

Why? I can work around this, but seems like it should work and return only four decimal places.

Answer 1

If your issues is all those 9999s, then the issue is floating point representation. Convert to a decimal to get fixed-point precision:

select round(cast(176 as float)/cast(492 as float), 4)::decimal(10, 4) as result;

Answer 2

Elaborating more on Gordon's answer -

So you’ve written some absurdly simple code, say for example:

0.1 + 0.2

and got a really unexpected result:

0.30000000000000004

Because internally, computers use a format (binary floating-point) that cannot accurately represent a number like 0.1, 0.2 or 0.3 at all.

When the code is compiled or interpreted, your “0.1” is already rounded to the nearest number in that format, which results in a small rounding error even before the calculation happens.

What can I do to avoid this problem?

That depends on what kind of calculations you’re doing.

• If you really need your results to add up exactly, especially when you work with money: use a decimal datatype.
• If you just don’t want to see all those extra decimal places: simply format your result rounded to a fixed number of decimal places when displaying it.

Shamelessly stolen from : Floating Point