Hi Alvaro,
Thank you for getting back to me. Well that is not correct the column is defined as numeric(20,0) because we don't
expectdecimal numbers in the column,
but operations on the value should not be dictated by the column definition. My table has millions of rows and cannot
changethe table definition due to number of rows and business purposes. The question is why is the result of the
operationdictated by the column definition?
If you just did select (3691635539999999999/10000000000) you would get the correct result minus all the numbers after
thedecimal point
SELECT (3691635539999999999/10000000000)
"369163553"
This seems to be bug, no? I have data centers with SQL Server and Oracle and they don't exhibit this behavior
Thank you again for getting back to me quickly. Looking forward to hearing from you
Thank you
Kaleb Akalework
-----Original Message-----
From: Alvaro Herrera <alvherre@2ndquadrant.com>
Sent: Friday, May 17, 2019 12:02 PM
To: Kaleb Akalework <kaleb.akalework@asg.com>; pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #15812: Select statement of a very big number, with a division operator seems to round up.
*** External email: Verify sender before opening attachments or links ***
On 2019-May-17, PG Bug reporting form wrote:
> create table test_table
> (
> REQUEST_UUID varchar(50) not null,
> BIG_NUM numeric(20,0) not null
> );
>
> INSERT INTO test_table (REQUEST_UUID, BIG_NUM) values ('TEST',
> 3691635539999999999); INSERT INTO test_table (REQUEST_UUID, BIG_NUM)
> values('TEST', 3691635530099999999); INSERT INTO test_table
> (REQUEST_UUID, BIG_NUM) values('TEST', 3691635530999999999);
>
> SELECT BIG_NUM, FLOOR(BIG_NUM/10000000000), BIG_NUM/10000000000 from
> test_table;
Well, your column definition has room for zero decimal places, so I'm not sure this result is all that surprising.
Maybeyou should cast the column to one that has a few decimal places, say
select bit_num::numeric(30,10) / 10000000000 from test_table; and see whether that helps your case.
--
Álvaro Herrera
http://TOC.ASG.COM:8080/?dmVyPTEuMDAxJiY2MjA1YzY5ZWNiMmRjZTgwOD01Q0RFREIwOF82NDEyOV8yOTEwXzEmJjNkYTNlNmVlYTQ1MDQwMT0xMjMyJiZ1cmw9aHR0cHMlM0ElMkYlMkZ3d3clMkUybmRRdWFkcmFudCUyRWNvbSUyRg==
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
