In my opinion the integer version doesn’t prevent the fraction problem at all, since the rounding works on fractions, even when the result is an integer. In fact it seems to introduce what I would consider a bug, compared to doing the conversion explicitly, for example in this query:
select round(0.5::real), round(0.5::double precision), round((0.5::double precision)::numeric), round(0.5)
The result will be 0, 0, 1, 1, which is very strange.
I think, at the very least, the implicit and explicit conversions, -- round(0.5::double precision) and round((0.5::double precision)::numeric) -- should give the same result, and they don’t. Which reinforces my intuition that I should avoid the real and double precision data types whenever possible.
Thanks,
Vlad
Vladimir Nicolici <vladnc@gmail.com> writes:
> I do that, but itÂ’s extremely annoying.
Well, if it rises to the level of extreme annoyance for you, there
is a simple solution:
create function round(float8, int) returns numeric as
'select round($1::numeric, $2)' language sql;
> Furthermore, since the single parameter version accepts double precision numbers, the imprecise representation excuse seems questionable at best.
The single-parameter version always rounds to integer, which avoids the
problem of not being able to represent decimal fractions exactly.
regards, tom lane