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: http://www.postgresql.org/mailpref/pgsql-hackers