RE: BUG #15812: Select statement of a very big number, with a division operator seems to round up.

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

In response to

Responses

Browse pgsql-bugs by date

  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.