Subject: Numeric Scale Differences

From: Nuno Sousa <[email-masked]>
Date: Thu, 21 Oct 2021 14:47:48 +0100

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

---

From: Tom Lane <[email-masked]>
Date: Thu, 21 Oct 2021 10:06:04 -0400

> 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

---

From: Nuno Sousa <[email-masked]>
Date: Thu, 21 Oct 2021 16:26:26 +0100

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