On Mon, May 29, 2017 at 4:19 PM, Kaiting Chen <ktche...@gmail.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@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.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.
