Re: Re: SQL error: function round(double precision, integer) does not exist

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

From: Tom Lane<mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us>
Sent: Thursday, May 25, 2017 21:22
To: Vladimir Nicolici<mailto:vladnc(at)gmail(dot)com>
Cc: pgsql-sql(at)postgresql(dot)org<mailto:pgsql-sql(at)postgresql(dot)org>
Subject: Re: [SQL] Re: SQL error: function round(double precision, integer) does not exist

Vladimir Nicolici <vladnc(at)gmail(dot)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