On Mon, May 29, 2017 at 4:19 PM, Kaiting Chen <ktchen14@xxxxxxxxx> 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.000000000000000000054210108624275221700372640043497085571 2890625
(1 row)
# SELECT 1 / (1 / (2::NUMERIC(96, 64) ^ 64));
?column?
------------------------------------------------------------ ---------------------------
18446744073709551616.00000000000000000000000000000000000000 00000000000000000000000000
(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@xxxxxxxxxxxxxx)
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.000000000000000000054210108
>(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;
SELECT 2::NUMERIC^ 64/1;
?column?
18446744073709551616.0000000000000000
18446744073709551616.000000000
--
Melvin Davidson
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.