Re: Why does numeric_out produce so many trailing zeros?

On Jan12, 2014, at 08:50 , David Rowley <dgrowleyml@gmail.com> wrote:
> I've been reading the documents on numeric and I can't find any
> information on the reason that a query like this:
> 
> test=# select n::numeric / 1 from generate_series(1,2) s(n);
>         ?column?
> ------------------------
>  1.00000000000000000000
>      2.0000000000000000
> (2 rows)
> 
> produces results that have so many trailing zeros. Also I'm wondering why
> the first row has 20 trailing zeros and the 2nd row has just 16?

According to the comment in select_div_scale()
 The result scale of a division isn't specified in any SQL standard. For PostgreSQL we select a result scale that will
giveat least NUMERIC_MIN_SIG_DIGITS significant digits, so that numeric gives a result no less accurate than float8;
butuse a scale not less than either input's display scale.
 

NUMERIC_MIN_SIG_DIGITS is 16, so that explains why you see at least 16
trailing zeros. You see more for 1/1 because the heuristics in
select_div_scale() can't determine that the result is larger than one
(due to the first digits being equal) and hence add zeros for safety.
Since NUMERIC internally uses base 10000, not base 10, once it adds
digits, it adds at least 4, because that corresponds to one digit in
base 10000.

That kind of makes sense if you interpret the number of trailing zeros
as an indicator of the precision of the result. There's then a
difference between '1.00' and just '1' - the former implies that the
result is correct up to at least 2 decimal digits, whereas in the latter
case the actual value may very well be 1.4. NUMERIC doesn't seem to use
that definition consistently, though - otherwise, the result of
'1.00' + '1' would have to be '2', not '2.00'. That behaviour seems
to be mandated by the SQL standard, though - the draft 2003 standard
says in part II subclause 6.26 "numeric value expression" paragraph (1b)
 The precision of the result of addition and subtraction is implementation-defined, and the scale is the maximum of S1
andS2.
 

> Is there any reason that we output any trailing zeros at all?

That also seems to be mandated by the standard. The draft says in
part II subclause 6.12 "cast specification" paragraph 10, which deals
with casts from numeric to character types
 Let YP be the shortest character string that conforms to the definition of <exact numeric literal> in Subclause 5.3,
"literal",whose scale is the same as the scale of SD and whose interpreted value is the absolute value of SV."
 

subclause 5.3 "literal" paragrpah (18) says
 The declared type of an <exact numeric literal> ENL is an implementation-defined exact numeric type whose scale is the
numberof <digit>s to the right of the <period>. There shall be an exact numeric type capable of representing the value
ofENL exactly.
 

best regards,
Florian Pflug