| From: | Kaiting Chen <ktchen14(at)gmail(dot)com> | 
|---|---|
| To: | pgsql-general(at)postgresql(dot)org | 
| Subject: | Does NUMERIC lose precision? | 
| Date: | 2017-05-29 20:19:12 | 
| Message-ID: | 7793DCC4-A6CD-410B-859F-C38FEB30DC85@gmail.com | 
| Views: | Whole Thread | Raw Message | Download mbox | Resend email | 
| Thread: | |
| Lists: | pgsql-general | 
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...
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Melvin Davidson | 2017-05-29 21:00:19 | Re: Does NUMERIC lose precision? | 
| Previous Message | PAWAN SHARMA | 2017-05-29 14:21:45 | Re: Oracle to PostgreSQL Migration. |