Postgres Pro
Downloads
Home   >   mailing lists

Thread: Numeric Scale Differences

Numeric Scale Differences

From
Nuno Sousa
Date:
Hi all,

I've run into an interesting issue when trying to do numeric math. I expected the following to return the same number of decimal digits in the fractional part:

select scale(1/0.6::numeric), scale(1/6::numeric);

But it returns a scale of 16 and 20, respectively.

Any idea why?
--
Nuno Sousa

Re: Numeric Scale Differences

From
Tom Lane
Date:
Nuno Sousa <nunofgs@gmail.com> writes:
> I've run into an interesting issue when trying to do numeric math. I
> expected the following to return the same number of decimal digits in the
> fractional part:

> select scale(1/0.6::numeric), scale(1/6::numeric);

Um ... why did you expect that?  The inputs to the divisions have
different scales:

# select scale(0.6::numeric), scale(6::numeric);
 scale | scale 
-------+-------
     1 |     0
(1 row)

so I find it unsurprising that the outputs do too.  Now the fact
that the output scales differ by 4 not 1 is indeed an implementation
artifact.  It stems from the numeric type working with base-10000
digits, so that scales that are multiples of 4 are most efficient,
hence division will always choose such an output scale.

            regards, tom lane

Re: Numeric Scale Differences

From
Nuno Sousa
Date:
Hi Tom,

Thanks for the quick response. In that case, do you think it's worth documenting the implementation artifact behavior on the docs?
--
Nuno Sousa


On Thu, Oct 21, 2021 at 3:06 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
Nuno Sousa <nunofgs@gmail.com> writes:
> I've run into an interesting issue when trying to do numeric math. I
> expected the following to return the same number of decimal digits in the
> fractional part:

> select scale(1/0.6::numeric), scale(1/6::numeric);

Um ... why did you expect that?  The inputs to the divisions have
different scales:

# select scale(0.6::numeric), scale(6::numeric);
 scale | scale
-------+-------
     1 |     0
(1 row)

so I find it unsurprising that the outputs do too.  Now the fact
that the output scales differ by 4 not 1 is indeed an implementation
artifact.  It stems from the numeric type working with base-10000
digits, so that scales that are multiples of 4 are most efficient,
hence division will always choose such an output scale.

                        regards, tom lane