Thread: Allow round() function to accept float and double precision

On Thu, 1 Dec 2022 at 07:39, Sayyid Ali Sajjad Rizavi
<sasrizavi@gmail.com> wrote:
>
> Whenever rounding a number to a fixed number of decimal points in a calculation, we need to cast the number into a
numericbefore using round((col1/100.0)::numeric, 2).
 
>
> It would be convenient for everyone if round() also accepts float and double precision.
>
> Is this something I could work with? And is that feasible?

I don't immediately see any issues with adding such a function.

We do have some weirdness in some existing overloaded functions.
pg_size_pretty() is an example.

If you run: SELECT pg_size_pretty(1000); you get:
ERROR:  function pg_size_pretty(integer) is not unique

That occurs because we don't know if we should promote the INT into a
BIGINT or into a NUMERIC. We have a pg_size_pretty() function for each
of those.  I don't think the same polymorphic type resolution problem
exists for REAL, FLOAT8 and NUMERIC. If a literal has a decimal point,
it's a NUMERIC, so it'll just use the numeric version of round().

I'm unsure what the repercussions of the fact that REAL and FLOAT8 are
not represented as decimals.  So I'm not quite sure what real
guarantees there are that the number is printed out with the number of
decimal places that you've rounded the number to.

Doing:

create function round(n float8, d int) returns float8 as $$ begin
return round(n::numeric, d)::float8; end; $$ language plpgsql;

and running things like:

select round(3.333333333333333::float8,10);

I'm not seeing any issues.

David

David Rowley <dgrowleyml@gmail.com> writes:
> We do have some weirdness in some existing overloaded functions.
> pg_size_pretty() is an example.

> If you run: SELECT pg_size_pretty(1000); you get:
> ERROR:  function pg_size_pretty(integer) is not unique

Yeah, you have to be careful about that when proposing to overload
a function name.

> That occurs because we don't know if we should promote the INT into a
> BIGINT or into a NUMERIC. We have a pg_size_pretty() function for each
> of those.  I don't think the same polymorphic type resolution problem
> exists for REAL, FLOAT8 and NUMERIC.

I would counsel against bothering with a REAL version.  FLOAT8 will
cover that case just fine.

> I'm unsure what the repercussions of the fact that REAL and FLOAT8 are
> not represented as decimals.

The main thing is that I think the output will still have to be
NUMERIC, or you're going to get complaints about "inaccurate"
results.  Before we got around to inventing infinities for NUMERIC,
that choice would have been problematic, but now I think it's OK.

            regards, tom lane

On Thu, 1 Dec 2022 at 15:41, David G. Johnston
<david.g.johnston@gmail.com> wrote:
> I don't get the point of adding a function here (or at least one called round) - the type itself is inexact so, as
yousay, it is actually more of a type conversion with an ability to specify precision, which is exactly what you get
todaywhen you write 1.48373::numeric(20,3) - though it is a bit annoying having to specify an arbitrary precision. 

An additional problem with that which you might have missed is that
you'd need to know what to specify in the precision part of the
typemod.  You might start getting errors one day if you don't select a
value large enough. That problem does not exist with round().  Having
to specify 131072 each time does not sound like a great solution, it's
not exactly a very memorable number.

David

On Thu, 1 Dec 2022 at 02:58, David Rowley <dgrowleyml@gmail.com> wrote:
>
> On Thu, 1 Dec 2022 at 15:41, David G. Johnston
> <david.g.johnston@gmail.com> wrote:
> > I don't get the point of adding a function here (or at least one called round) - the type itself is inexact so, as
yousay, it is actually more of a type conversion with an ability to specify precision, which is exactly what you get
todaywhen you write 1.48373::numeric(20,3) - though it is a bit annoying having to specify an arbitrary precision. 
>
> An additional problem with that which you might have missed is that
> you'd need to know what to specify in the precision part of the
> typemod.  You might start getting errors one day if you don't select a
> value large enough. That problem does not exist with round().  Having
> to specify 131072 each time does not sound like a great solution, it's
> not exactly a very memorable number.
>

I don't really see the point of such a function either.

Casting to numeric(1000, n) will work fine in all cases AFAICS (1000
being the maximum allowed precision in a numeric typemod, and somewhat
more memorable).

Note that double precision numbers range in magnitude from something
like 2.2e-308 to 1.8e308, so you won't ever get an error (except, I
suppose, if you also chose "n" larger than 692 or so, but that would
be silly, given the input).


> > At present round does allow you to specify a negative position to round at positions to the left of the decimal
point(this is undocumented though...) which the actual cast cannot do, but that seems like a marginal case. 

Note that, as of PG15, "n" can be negative in such typemods, if you
want to round before the decimal point.

The fact that passing a negative scale to round() isn't documented
does seem like an oversight though...

Regards,
Dean

Dean Rasheed <dean.a.rasheed@gmail.com> writes:
> I don't really see the point of such a function either.
> Casting to numeric(1000, n) will work fine in all cases AFAICS (1000
> being the maximum allowed precision in a numeric typemod, and somewhat
> more memorable).

Right, but I think what the OP wants is to not have to think about
whether the input is of exact or inexact type.  That's easily soluble
locally by making your own function:

create function round(float8, int) returns numeric
  as $$select pg_catalog.round($1::pg_catalog.numeric, $2)$$
  language sql strict immutable parallel safe;

but I'm not sure that the argument for it is strong enough to
justify putting it into Postgres.

> The fact that passing a negative scale to round() isn't documented
> does seem like an oversight though...

Agreed, will do something about that.

            regards, tom lane

On Thu, 1 Dec 2022 at 21:55, Dean Rasheed <dean.a.rasheed@gmail.com> wrote:
> Casting to numeric(1000, n) will work fine in all cases AFAICS (1000
> being the maximum allowed precision in a numeric typemod, and somewhat
> more memorable).

I wasn't aware of the typemod limit.

I don't really agree that it will work fine in all cases though. If
the numeric has more than 1000 digits left of the decimal point then
the method won't work at all.

# select length(('1' || repeat('0',2000))::numeric(1000,0)::text);
ERROR:  numeric field overflow
DETAIL:  A field with precision 1000, scale 0 must round to an
absolute value less than 10^1000.

No issue with round() with the same number.

# select length(round(('1' || repeat('0',2000))::numeric,0)::text);
 length
--------
   2001

David

David Rowley <dgrowleyml@gmail.com> writes:
> I don't really agree that it will work fine in all cases though. If
> the numeric has more than 1000 digits left of the decimal point then
> the method won't work at all.

But what we're talking about is starting from a float4 or float8
input, so it can't be more than ~308 digits.

            regards, tom lane

On Fri, 2 Dec 2022 at 09:02, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> David Rowley <dgrowleyml@gmail.com> writes:
> > I don't really agree that it will work fine in all cases though. If
> > the numeric has more than 1000 digits left of the decimal point then
> > the method won't work at all.
>
> But what we're talking about is starting from a float4 or float8
> input, so it can't be more than ~308 digits.

I may have misunderstood. I thought David J was proposing this as a
useful method for rounding numeric too. Re-reading what he wrote, I no
longer think he was.

David