Thread: [GENERAL] Numeric numbers

Hi all. I have silly question. Look at "numeric" type. According to
docs it must be "up to 131072 digits before the decimal point; up to
16383 digits after the decimal point". Well, lets see.

=> select 1::numeric/3;
        ?column?        
------------------------
 0.33333333333333333333

Well, I expect 16383 digits after ".". But nope.
=> select (1::numeric/3-0.33333333333333333333)*1e20;
        ?column?        
------------------------
 0.00000000000000000000
=> select (1::numeric/3-0.33333333333333333332)*1e20;
        ?column?        
------------------------
 1.00000000000000000000

There is only 20 "3" after ".". Well, may be this is not a problem, but
why are they infinite number of "0" after the point? I can write even

=> select (1::numeric/3-0.33333333333333333333)*1e100000;
        ?column?        
------------------------
 0.00000000000000000000

Result the same. According to the docs: "Numeric values are physically
stored without any extra leading or trailing zeroes."

On Sat, Sep 2, 2017 at 4:16 PM, Олег Самойлов <olleg@mipt.ru> wrote:
> Hi all. I have silly question. Look at "numeric" type. According to
> docs it must be "up to 131072 digits before the decimal point; up to
> 16383 digits after the decimal point". Well, lets see.
>
> => select 1::numeric/3;
>         ?column?
> ------------------------
>  0.33333333333333333333

=> select 1::numeric(100,90)/3;
                                           ?column?
----------------------------------------------------------------------------------------------
 0.333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333
(1 row)

It's probably doing 1(integer) => double precioson => numeric(20) or
something similar if you do not specify.

Francisco Olarte.

On Sat, Sep 2, 2017 at 10:10 AM, Melvin Davidson <melvin6925@gmail.com> wrote:
>
>
> On Sat, Sep 2, 2017 at 11:54 AM, Francisco Olarte <folarte@peoplecall.com> wrote:
>>
>> On Sat, Sep 2, 2017 at 4:16 PM, Олег Самойлов <olleg@mipt.ru> wrote:
>> > Hi all. I have silly question. Look at "numeric" type. According to
>> > docs it must be "up to 131072 digits before the decimal point; up to
>> > 16383 digits after the decimal point". Well, lets see.
>> >
>> > => select 1::numeric/3;
>> >         ?column?
>> > ------------------------
>> >  0.33333333333333333333
>>
>> => select 1::numeric(100,90)/3;
>>                                            ?column?
>> ----------------------------------------------------------------------------------------------
>>  0.333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333
>> (1 row)
>>
>> It's probably doing 1(integer) => double precioson => numeric(20) or
>> something similar if you do not specify.
>>
>> Francisco Olarte.
>>
>>
>> --
>> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-general
>
>
> Franciso,
>
> Per the docs, is is not "must be', it is "up to 131072 digits before the decimal point; up to 16383 digits after the
decimalpoint". 
> https://www.postgresql.org/docs/9.6/static/datatype-numeric.html#DATATYPE-NUMERIC-TABLE
>
> YOU have specified a precision of numeric(100,90), which means 90 decimals and that is exactly what you got!
> The result is correct, so what is your question?
>
>

Huh. I'm guessing that the cast is the limit here:

smarlowe=# select 1::numeric(1001,500);
ERROR:  NUMERIC precision 1001 must be between 1 and 1000
LINE 1: select 1::numeric(1001,500);

On Sat, 2017-09-02 at 17:54 +0200, Francisco Olarte wrote:
> It's probably doing 1(integer) => double precioson => numeric(20) or
> something similar if you do not specify.
> 
> Francisco Olarte.

Well, the question was not only about why there is only 20 "3" after
point, I suspect this (may be it's not good enough documented, but
reasonable), but also about why are there infinite amount of "0" after
point if I subtract "3"s.

Vincenzo:

On Sat, Sep 2, 2017 at 6:20 PM, Vincenzo Romano
<vincenzo.romano@notorand.it> wrote:
> And I think Francisco is asking why only 20 digits.

No need to think, I wasn't asking anything.

I'm used to FLOAT ( I think actually DP )numbers being converted to
numeric(,20), and I normally never cast to unspecified numeric
precision ( except for toy one liners and the like ), and I was trying
to show the OP why he got 20.

Francisco Olarte.

CCing the list ( hint: use reply all in your MUA, otherwhise people
will loose the thread, this message came only for me. If that was what
you wnated, please indicate so in future messages, as the custom in
this list is to reply to list + posters )

On Sat, Sep 2, 2017 at 6:21 PM, Олег Самойлов <olleg@mipt.ru> wrote:
> On Sat, 2017-09-02 at 17:54 +0200, Francisco Olarte wrote:
>> It's probably doing 1(integer) => double precioson => numeric(20) or
>> something similar if you do not specify.
>>
>> Francisco Olarte.
>
> Well, the question was not only about why there is only 20 "3" after
> point, I suspect this (may be it's not good enough documented, but
> reasonable), but also about why are there infinite amount of "0" after
> point if I subtract "3"s.

Well, I just skipped over the rest of the code. I consider casting to
unespecified numeric widths a very bad habit and did not want to
encourage it.

Francisco Olarte.

On Sat, 2017-09-02 at 18:58 +0200, Francisco Olarte wrote:
> CCing the list ( hint: use reply all in your MUA, otherwhise people
> will loose the thread, this message came only for me. If that was
> what
> you wnated, please indicate so in future messages, as the custom in
> this list is to reply to list + posters )

Got it.

>
> On Sat, Sep 2, 2017 at 6:21 PM, Олег Самойлов <olleg@mipt.ru> wrote:
> >
> > On Sat, 2017-09-02 at 17:54 +0200, Francisco Olarte wrote:
> > >
> > > It's probably doing 1(integer) => double precioson => numeric(20)
> > > or
> > > something similar if you do not specify.
> > >
> > > Francisco Olarte.
> > Well, the question was not only about why there is only 20 "3"
> > after
> > point, I suspect this (may be it's not good enough documented, but
> > reasonable), but also about why are there infinite amount of "0"
> > after
> > point if I subtract "3"s.
> Well, I just skipped over the rest of the code. I consider casting to
> unespecified numeric widths a very bad habit and did not want to
> encourage it.
>
> Francisco Olarte.

There is nothing in documentation that this casting is a very bad
habit. And this is most interesting part, about infinite zeros. Looked
like a bug.

Олег:

On Sat, Sep 2, 2017 at 7:04 PM, Олег Самойлов <olleg@mipt.ru> wrote:
...

>> Well, I just skipped over the rest of the code. I consider casting to
>> unespecified numeric widths a very bad habit and did not want to
>> encourage it.
> There is nothing in documentation that this casting is a very bad
> habit. And this is most interesting part, about infinite zeros. Looked
> like a bug.

It's not in the documentation, as it rarely deals with habits and
styles. And many people could consider it perfectly kosher. It's just
something  *I* consider a bad habit personally, feel free to use it as
much as you like, or to recomend it as good if you want. Normally I
wouldn't even mention it, as I did not in my first response, I just
did to explain why I ignored the tail.



Francisco Olarte.

There is only 20 "3" after ".". Well, may be this is not a problem, but
why are they infinite number of "0" after the point? I can write even

=> select (1::numeric/3-0.33333333333333333333)*1e100000;
        ?column?        
------------------------
 0.00000000000000000000

Result the same. According to the docs: "Numeric values are physically
stored without any extra leading or trailing zeroes."

On Sat, 2017-09-02 at 11:41 -0700, David G. Johnston wrote:
> > There is only 20 "3" after ".". Well, may be this is not a problem,
> > but
> > why are they infinite number of "0" after the point? I can write
> > even
> >
> > => select (1::numeric/3-0.33333333333333333333)*1e100000;
> >         ?column?        
> > ------------------------
> >  0.00000000000000000000
> >
> > Result the same. According to the docs: "Numeric values are
> > physically
> > stored without any extra leading or trailing zeroes."
> >
> >
> What you see on the screen is not what is physically stored.  The
> documentation about the numeric type on the server is accurate, but
> apparently the psql application takes some liberties as to what it
> chooses to display.   It's display rules for numeric don't seem to be
> user documented or configurable.
>
> David J,

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?

=?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

Thanks for the answer, Tom. Now it's become clear.

On Sat, 2017-09-02 at 16:20 -0400, Tom Lane wrote:
> 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.

You are right. I am rereading the official documentation of PostgreSQL
(9.6) to refresh it in memory. But didn't see any description about
"display scale", only about "scale", which is part of type definition.
I just checked it by google.

https://www.google.ru/search?q=site%3Ahttps%3A%2F%2Fwww.postgresql.org%
2Fdocs%2F9.6%2Fstatic%2F+%22display+scale%22&oq=site%3Ahttps%3A%2F%2Fww
w.postgresql.org%2Fdocs%2F9.6%2Fstatic%2F+%22display+scale%22&aqs=chrom
e..69i57j69i58.15543j0j8&sourceid=chrome&ie=UTF-8

"Display scale" exist only inside release notes. What is interesting,
there is even function scale(numeric) that shows parameter "display
scale" (according to release notes), but according to description in
documentation "scale of the argument (the number of decimal digits in
the fractional part)". May be this definitions are the same, but this
was not obvious. :) I checked:

=> select 1::numeric/3-0.33333333333333333333;
        ?column?        
------------------------
 0.00000000000000000000

=> select scale(1::numeric/3-0.33333333333333333333);
 scale 
-------
    20


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

Yep, it's reasonable, that's why I checked this example. But it is not
documented in description of numeric type and in chapter "Mathematical
Functions and Operators".