From: | Kaleb Akalework <kaleb(dot)akalework(at)asg(dot)com> |
---|---|
To: | Alvaro Herrera <alvherre(at)2ndquadrant(dot)com>, "pgsql-bugs(at)lists(dot)postgresql(dot)org" <pgsql-bugs(at)lists(dot)postgresql(dot)org> |
Subject: | RE: BUG #15812: Select statement of a very big number, with a division operator seems to round up. |
Date: | 2019-05-17 16:10:52 |
Message-ID: | 982ab3c88ea14f4593cc45b26acb44f6@asg.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
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 expect decimal 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 change the table definition due to number of rows and business purposes. The question is why is the result of the operation dictated by the column definition?
If you just did select (3691635539999999999/10000000000) you would get the correct result minus all the numbers after the decimal 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(at)2ndquadrant(dot)com>
Sent: Friday, May 17, 2019 12:02 PM
To: Kaleb Akalework <kaleb(dot)akalework(at)asg(dot)com>; pgsql-bugs(at)lists(dot)postgresql(dot)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. Maybe you 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
From | Date | Subject | |
---|---|---|---|
Next Message | Andres Freund | 2019-05-17 16:23:22 | Re: BUG #15812: Select statement of a very big number, with a division operator seems to round up. |
Previous Message | Alvaro Herrera | 2019-05-17 16:02:11 | Re: BUG #15812: Select statement of a very big number, with a division operator seems to round up. |