sql - 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.

question from:https://stackoverflow.com/questions/65943500/redshift-round-function-doesnt-round-in-some-cases

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;