On Jan12, 2014, at 08:50 , David Rowley <dgrowle...@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 give at least
  NUMERIC_MIN_SIG_DIGITS significant digits, so that numeric gives a
  result no less accurate than float8; but use 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 and S2.

> 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 number of
  <digit>s to the right of the <period>. There shall be an exact numeric
  type capable of representing the value of ENL exactly.

best regards,
Florian Pflug

Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:

Reply via email to