Re: [GENERAL] Does NUMERIC lose precision?

[Tom Lane]

Kaiting Chen  writes:
> Hi everyone. I’m trying to perform some exact precision arithmetic with 
> PostgreSQL’s NUMERIC type. However I can’t seem to get the unparameterized 
> NUMERIC type to perform exact precision arithmetic:

Division is inherently inexact, except in special cases.  If you think
that 1/(2^64) should be carried out to enough digits to be exact,
what would you have us do with 1/3?

The actual behavior is that it will choose a result scale (number of
digits after the decimal point) that is dependent on the scales of the
input arguments, but not on their precise values.  I don't recall the
details beyond that.

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] Does NUMERIC lose precision?

[Melvin Davidson]

On Mon, May 29, 2017 at 4:19 PM, Kaiting Chen  wrote:

> Hi everyone. I’m trying to perform some exact precision arithmetic with
> PostgreSQL’s NUMERIC type. However I can’t seem to get the unparameterized
> NUMERIC type to perform exact precision arithmetic:
>
> # SELECT 2::NUMERIC ^ 64;
>?column?
> ---
>  18446744073709551616.
> (1 row)
>
> While the above operation works fine once I divide 1 by that number the
> result is an inexact decimal number:
>
> # SELECT 1 / (2::NUMERIC ^ 64);
> ?column?
> 
>  0.00054210108624275222
> (1 row)
>
> It doesn't seem to be an issue with the output either as taking the
> reciprocal yields a different number than I started with:
>
> # SELECT 1 / (1 / (2::NUMERIC ^ 64));
>  ?column?
> ---
>  18446744073709551514.042092759729171265910020841463748922
> (1 row)
>
> The only way to get an exact result is by specifying an explicit precision
> and scale:
>
> # SELECT 1 / (2::NUMERIC(96, 64) ^ 64);
>   ?column?
> 
>  0.000542101086242752217003726400434970855712890625
> (1 row)
>
> # SELECT 1 / (1 / (2::NUMERIC(96, 64) ^ 64));
>?column?
> 
> ---
>  18446744073709551616.00
> 00
> (1 row)
>
> However this does not seem intuitive from the documentation which states
> that:
>
> Specifying:
>
> NUMERIC
>
> without any precision or scale creates a column in which numeric values of
> any precision and scale can be stored, up to the implementation limit on
> precision. A column of this kind will not coerce input values to any
> particular scale...
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

>While the above operation works fine once I divide 1 by that number the
result is an inexact decimal number:
># SELECT 1 / (2::NUMERIC ^ 64);
  ?   ?column?
>
>0.00054210108624275222
>(1 row)


*That is the same answer you get when you use any calculator. *


*Are you sure you did not meanSELECT 2::NUMERIC^ 64/1;*


*?column?18446744073709551616.*

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

[GENERAL] Does NUMERIC lose precision?

[Kaiting Chen]

Hi everyone. I’m trying to perform some exact precision arithmetic with 
PostgreSQL’s NUMERIC type. However I can’t seem to get the unparameterized 
NUMERIC type to perform exact precision arithmetic:

# SELECT 2::NUMERIC ^ 64;
   ?column?
---
 18446744073709551616.
(1 row)

While the above operation works fine once I divide 1 by that number the result 
is an inexact decimal number:

# SELECT 1 / (2::NUMERIC ^ 64);
?column?

 0.00054210108624275222
(1 row)

It doesn't seem to be an issue with the output either as taking the reciprocal 
yields a different number than I started with:

# SELECT 1 / (1 / (2::NUMERIC ^ 64));
 ?column?  
---
 18446744073709551514.042092759729171265910020841463748922
(1 row)

The only way to get an exact result is by specifying an explicit precision and 
scale:

# SELECT 1 / (2::NUMERIC(96, 64) ^ 64);
  ?column?  

 0.000542101086242752217003726400434970855712890625
(1 row)

# SELECT 1 / (1 / (2::NUMERIC(96, 64) ^ 64));
   ?column? 
   
---
 
18446744073709551616.
(1 row)

However this does not seem intuitive from the documentation which states that:

Specifying:

NUMERIC

without any precision or scale creates a column in which numeric values of any 
precision and scale can be stored, up to the implementation limit on precision. 
A column of this kind will not coerce input values to any particular scale...


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