Re: [GENERAL] Numeric numbers

[Олег Самойлов]

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." (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=site%3Ahttps%3A%2F%2Fww
w.postgresql.org%2Fdocs%2F9.6%2Fstatic%2F+%22display+scale%22=chrom
e..69i57j69i58.15543j0j8=chrome=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.;
?column?

 0.

=> select scale(1::numeric/3-0.);
 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".


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Numeric numbers

[Tom Lane]

=?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?=  
writes:
> What did you mean? 0. is not 0 indeed, but wrongly
> show as 0. Or it's 0, but badly formated as 0.?

Really?

regression=# select 0.;
?column?

 0.
(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.::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." (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.
(1 row)

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

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

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


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Numeric numbers

[Олег Самойлов]

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.)*1e10;
> > ?column?        
> > 
> >  0.
> > 
> > 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. is not 0 indeed, but wrongly
show as 0. Or it's 0, but badly formated as 0.?


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Numeric numbers

[David G. Johnston]

On Saturday, September 2, 2017, Олег Самойлов  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.)*1e10;
> ?column?
> 
>  0.
>
> 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,

Re: [GENERAL] Numeric numbers

[Francisco Olarte]

Олег:

On Sat, Sep 2, 2017 at 7:04 PM, Олег Самойлов  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.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Numeric numbers

[Олег Самойлов]

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, Олег Самойлов  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.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Numeric numbers

[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, Олег Самойлов  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.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Numeric numbers

[Francisco Olarte]

Vincenzo:

On Sat, Sep 2, 2017 at 6:20 PM, Vincenzo Romano
 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.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Numeric numbers

[Олег Самойлов]

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.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Numeric numbers

[Scott Marlowe]

On Sat, Sep 2, 2017 at 10:10 AM, Melvin Davidson  wrote:
>
>
> On Sat, Sep 2, 2017 at 11:54 AM, Francisco Olarte  
> wrote:
>>
>> On Sat, Sep 2, 2017 at 4:16 PM, Олег Самойлов  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.
>>
>> => select 1::numeric(100,90)/3;
>>?column?
>> --
>>  
>> 0.33
>> (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 decimal point".
> 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);


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Numeric numbers

[Vincenzo Romano]

2017-09-02 18:10 GMT+02:00 Melvin Davidson :

>
> On Sat, Sep 2, 2017 at 11:54 AM, Francisco Olarte 
> wrote:
>
>> On Sat, Sep 2, 2017 at 4:16 PM, Олег Самойлов  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.
>>
>> => select 1::numeric(100,90)/3;
>>?column?
>> 
>> --
>>  0.3
>> 3
>> (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 decimal point".*
>
> *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?*
>
> --
> *Melvin Davidson*
> I reserve the right to fantasize.  Whether or not you
> wish to share my fantasy is entirely up to you.
>


The original snippet reads:

select 1::numeric/3;

And I think Francisco is asking why only 20 digits.

-- 
Vincenzo Romano - NotOrAnd.IT
Information Technologies
--
NON QVIETIS MARIBVS NAVTA PERITVS

Re: [GENERAL] Numeric numbers

[Vincenzo Romano]

Quoting from documentation:

"without any precision or scale [you get] a column in which values of any
precision and scale can be stored, up to the implementation limit on
precision."

I suspect the cast is doing some precision limitation.

--
Vincenzo Romano - NotOrAnd.IT
Information Technologies
--
NON QVIETIS MARIBVS NAVTA PERITVS

Il 02 set 2017 17:57, "Francisco Olarte"  ha
scritto:

> On Sat, Sep 2, 2017 at 4:16 PM, Олег Самойлов  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.
>
> => select 1::numeric(100,90)/3;
>?column?
> 
> --
>  0.
> 33
> (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
>

Re: [GENERAL] Numeric numbers

[Melvin Davidson]

On Sat, Sep 2, 2017 at 11:54 AM, Francisco Olarte 
wrote:

> On Sat, Sep 2, 2017 at 4:16 PM, Олег Самойлов  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.
>
> => select 1::numeric(100,90)/3;
>?column?
> 
> --
>  0.
> 33
> (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 decimal point".*

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

-- 
*Melvin Davidson*
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

Re: [GENERAL] Numeric numbers

[Francisco Olarte]

On Sat, Sep 2, 2017 at 4:16 PM, Олег Самойлов  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.

=> select 1::numeric(100,90)/3;
   ?column?
--
 
0.33
(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