Re: [GENERAL] Numeric numbers - Mailing list pgsql-general

=?UTF-8?Q?=D0=9E=D0=BB=D0=B5=D0=B3_?= =?UTF-8?Q?=D0=A1=D0=B0=D0=BC=D0=BE=D0=B9=D0=BB=D0=BE=D0=B2?= <olleg@mipt.ru>
writes:
> What did you mean? 0.00000000000000000000 is not 0 indeed, but wrongly
> show as 0. Or it's 0, but badly formated as 0.00000000000000000000?

Really?

regression=# select 0.00000000000000000000;
        ?column?
------------------------
 0.00000000000000000000
(1 row)

However, it's true that those trailing zeroes aren't physically stored:

regression=# select pg_column_size(0.00::numeric);
 pg_column_size
----------------
              6
(1 row)

regression=# select pg_column_size(0.00000000000000000000::numeric);
 pg_column_size
----------------
              6
(1 row)

The information you're missing here is that a numeric value carries a
"display scale" value which indicates how many fractional digits to print.
So "0.0" (scale 1) and "0.00000000000000000000" (scale 20) print
differently, but they occupy the same amount of storage because the
trailing zeroes are stripped for storage.

Likewise, the documentation you started with was talking about the
physical limits of the storage format, not about the actual behavior
of any particular numeric operation.

As far as the 1/3 example goes, the division has to stop somewhere;
we can't store an infinite number of digits.  We could carry out the
division to the physical limit of what numeric could store, but nobody
would like that behavior.  The current behavior, cf select_div_scale(),
is

     * 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.

So in this example you get 20 digits by default:

regression=# select 1::numeric / 3;
        ?column?
------------------------
 0.33333333333333333333
(1 row)

but you can get more by specifying a larger scale for either input:

regression=# select 1::numeric(50,40) / 3;
                  ?column?
--------------------------------------------
 0.3333333333333333333333333333333333333333
(1 row)

There aren't any hidden digits beyond what you can see; the result
is what it is.  Claiming that there are an infinite number of zeroes
after it seems rather beside the point.

            regards, tom lane