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 numeric before 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 Wed, Nov 30, 2022 at 6:45 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:

David Rowley <dgrowleyml@gmail.com> writes:

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.

I don't get the point of adding a function here (or at least one called
round) - the type itself is inexact so, as you say, it is actually more of
a type conversion with an ability to specify precision, which is exactly
what you get today when you write 1.48373::numeric(20,3) - though it is a
bit annoying having to specify an arbitrary precision.

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.

Maybe call it: make_exact(numeric, integer) ?

I do get a feeling like I'm being too pedantic here though...

David J.

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 you say, it is actually more of a type conversion with an ability to specify precision, which is exactly what you get today when 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 you say, it is actually more of a type conversion with an ability to specify precision, which is exactly what you get today when 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, Dec 1, 2022 at 7:39 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:

Dean Rasheed <dean.a.rasheed@gmail.com> writes:

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

Agreed, will do something about that.

Thanks. I'm a bit surprised you left "Rounds v to s decimal places." alone
though. I feel like the prose should also make clear that positions to the
left of the decimal, which are not conventionally considered decimal
places, can be identified.

Rounds v at s digits before or after the decimal place.

The examples will hopefully clear up any off-by-one concerns that someone
may have.

David J.

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

On Thu, Dec 1, 2022 at 2:21 PM David Rowley <dgrowleyml@gmail.com> wrote:

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.

I was not, my response was that what is being asked for is basically a cast
from float to numeric, and doing that via a "round()" function seems odd.
And we can handle the desired rounding aspect of that process already via
the existing numeric(1000, n) syntax.

David J.