Does NUMERIC lose precision?

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

Does NUMERIC lose precision?

Kaiting Chen-2
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.0000000000000000
(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.000000000000000000054210108624275222
(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.0000000000000000000542101086242752217003726400434970855712890625
(1 row)

# SELECT 1 / (1 / (2::NUMERIC(96, 64) ^ 64));
                                       ?column?                                        
---------------------------------------------------------------------------------------
 18446744073709551616.0000000000000000000000000000000000000000000000000000000000000000
(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 ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Reply | Threaded
Open this post in threaded view
|

Re: Does NUMERIC lose precision?

Melvin Davidson-5


On Mon, May 29, 2017 at 4:19 PM, Kaiting Chen <[hidden email]> 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.0000000000000000
(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.000000000000000000054210108624275222
(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.0000000000000000000542101086242752217003726400434970855712890625
(1 row)

# SELECT 1 / (1 / (2::NUMERIC(96, 64) ^ 64));
                                       ?column?
---------------------------------------------------------------------------------------
 18446744073709551616.0000000000000000000000000000000000000000000000000000000000000000
(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 ([hidden email])
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.000000000000000000054210108624275222
>(1 row)

That is the same answer you get when you use any calculator.
Are you sure you did not mean
SELECT 2::NUMERIC^ 64/1;
?column?
18446744073709551616.0000000000000000

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

Reply | Threaded
Open this post in threaded view
|

Re: Does NUMERIC lose precision?

Tom Lane-2
In reply to this post by Kaiting Chen-2
Kaiting Chen <[hidden email]> 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 ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general