Why 5.0 / 2 returns 2.5000000000000000 (scale of 16) instead of 2.5 (scale of 1)?

Question

Fiddle

select 5.0 / 2
     , pg_typeof(5.0 / 2);

	pg_typeof
2.5000000000000000	numeric

Why the result is 2.5000000000000000? When we insert 2.5 into a numeric column or select 2.5 from a numeric column we don't get extra zeroes, we just get 2.5.

I expected 2.5 with scale of 1, without the extra 0's all the way up to scale of 16.

Answer 1

The documentation

The operator responsible for numeric/numeric division defaults to scale of 16. It's not explicitly stated in the doc, although there is a hint: that exact scale being shown in the example.

numeric_type / numeric_type → numeric_type  Division (for integral types, division truncates the result towards zero)  5.0 / 2 → 2.5000000000000000

In cases like this, you can always take a look at the source to clarify things. Before going into that, I'll mention that the table linked above lists a few functions that might interest you:

min_scale ( numeric ) → integer  Minimum scale (number of fractional decimal digits) needed to represent the supplied value precisely  min_scale(8.4100) → 2
scale ( numeric ) → integer  Scale of the argument (the number of decimal digits in the fractional part)  scale(8.4100) → 4
trim_scale ( numeric ) → numeric  Reduces the value's scale (number of fractional decimal digits) by removing trailing zeroes  trim_scale(8.4100) → 8.41

You can use them to inspect, estimate and apply the minimum scale automatically. You can also use a cast with precision and scale specified as typemod in parentheses ::numeric(2,1):
_{demo at db<>fiddle}

select a
     , b
     , numeric_div_result
     , scale(numeric_div_result)
     , min_scale(numeric_div_result)
     , trim_scale(numeric_div_result)
     , "::numeric(2,1)"
from(values(5.,2)
          ,(5.,4)
          ,(0.,1)
          ,(1.,3)
          ,(31,29.)
          ,(31,29+0e-21))as val(a,b)
cross join lateral(select a/b as numeric_div_result)
cross join lateral(select numeric_div_result::numeric(2,1) as "::numeric(2,1)");

a	b	numeric_div_result	scale	min_scale	trim_scale	::numeric(2,1)
5	2	2.5000000000000000	16	1	2.5	2.5
5	4	1.2500000000000000	16	2	1.25	1.3
0	1	0.00000000000000000000	20	0	0	0.0
1	3	0.33333333333333333333	20	20	0.33333333333333333333	0.3
31	29	1.0689655172413793	16	16	1.0689655172413793	1.1

The second example shows why trim_scale() might be better than assuming a target scale in a fixed cast. The last example shows the trick with the addition of a high-precision zero to force a higher precision result:

a	b	numeric_div_result	scale	min_scale	trim_scale	::numeric(2,1)
31	29.000000000000000000000	1.068965517241379310345	21	21	1.068965517241379310345	1.1

A fun addition is 1/998001: the digits of its result are a repeating sequence of all three-digit numbers except 998. Unfortunately, it runs out of the 1000 NUMERIC_MAX_PRECISION limiting division results, before getting very far:

select 1/(998001+0e-1337);

0.0000010020030040050060070080090100110120130140150160170180190200210220230240250260270280290300310320330340350360370380390400410420430440450460470480490500510520530540550560570580590600610620630640650660670680690700710720730740750760770780790800810820830840850860870880890900910920930940950960970980991001011021031041051061071081091101111121131141151161171181191201211221231241251261271281291301311321331341351361371381391401411421431441451461471481491501511521531541551561571581591601611621631641651661671681691701711721731741751761771781791801811821831841851861871881891901911921931941951961971981992002012022032042052062072082092102112122132142152162172182192202212222232242252262272282292302312322332342352362372382392402412422432442452462472482492502512522532542552562572582592602612622632642652662672682692702712722732742752762772782792802812822832842852862872882892902912922932942952962972982993003013023033043053063073083093103113123133143153163173183193203213223233243253263273283293303313323

If you're wondering why numeric/int division ends up executing a numeric/numeric, refer to Chapter 10. Type Conversion: 10.2. Operators. There's no built-in numeric/int operator, so the right operand has to be converted.

The reason a literal 5.0 is a numeric and not real, float, smallint, int or bigint, while 2 is an int and none of the other two integer types, can be found in 4.1.2.6. Numeric Constants:

A numeric constant that contains neither a decimal point nor an exponent is initially presumed to be type integer if its value fits in type integer (32 bits); otherwise it is presumed to be type bigint if its value fits in type bigint (64 bits); otherwise it is taken to be type numeric.

---

The source

This is a similar thread to this one:

• How to divide two NUMERIC values and get a result that has the maximum number of digits the NUMERIC type supports?

The difference is that it's the opposite question: that one's about maximising the scale, this one's about reducing it. In both cases the starting point is about the same: postgres/src/include/utils /numeric.h:45:

/*
 * For inherently inexact calculations such as division and square root,
 * we try to get at least this many significant digits; the idea is to
 * deliver a result no worse than float8 would.
 */
#define NUMERIC_MIN_SIG_DIGITS      16

It's used by select_div_scale in postgres/src/backend/utils/adt /numeric.c:10128

/*
 * Default scale selection for division
 *
 * Returns the appropriate result scale for the division result.
 */
static int
select_div_scale(const NumericVar *var1, const NumericVar *var2)

Which is called by numeric_div found in postgres/src/backend/utils/adt/numeric.c:3133:

/*
 * numeric_div() -
 *
 *  Divide one numeric into another
 */
Datum
numeric_div(PG_FUNCTION_ARGS)
{
    Numeric     num1 = PG_GETARG_NUMERIC(0);
    Numeric     num2 = PG_GETARG_NUMERIC(1);
    Numeric     res;

    res = numeric_div_opt_error(num1, num2, NULL);

    PG_RETURN_NUMERIC(res);
}

And that's the real function behind numeric/numeric operator you see in pg_operator.oprcode:

select oid::regoperator
      ,oprcode 
from pg_operator
where oprname='/'
and oprleft='numeric'::regtype
and oprright='numeric'::regtype;

oid	oprcode
/(numeric,numeric)	numeric_div

The 1.0/3 in the example from the other thread does a bit more, but in your case NUMERIC_MIN_SIG_DIGITS is where the scale estimation ends. Note that this default scale isn't the same one as the default scale of numeric type, as described in the doc:

Specifying:

NUMERIC

without any precision or scale creates an “unconstrained numeric” column in which numeric values of any length can be stored, up to the implementation limits. A column of this kind will not coerce input values to any particular scale, whereas numeric columns with a declared scale will coerce input values to that scale.

What NUMERIC_MIN_SIG_DIGITS dictates here is only the scale of the result of division using /.