From: | Melvin Davidson <melvin6925(at)gmail(dot)com> |
---|---|
To: | Kaiting Chen <ktchen14(at)gmail(dot)com> |
Cc: | "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Does NUMERIC lose precision? |
Date: | 2017-05-29 21:00:19 |
Message-ID: | CANu8FixUG=MTqkD-TOj2HPtp9HULqFw0hxi_RsCrTS0W+u3Hyg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Mon, May 29, 2017 at 4:19 PM, Kaiting Chen <ktchen14(at)gmail(dot)com> 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.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(at)postgresql(dot)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.000000000000000000054210108624275222
>(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.0000000000000000*
--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2017-05-29 21:24:10 | Re: Does NUMERIC lose precision? |
Previous Message | Kaiting Chen | 2017-05-29 20:19:12 | Does NUMERIC lose precision? |