From: | Andres Freund <andres(at)anarazel(dot)de> |
---|---|
To: | Kaleb Akalework <kaleb(dot)akalework(at)asg(dot)com> |
Cc: | 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:23:22 |
Message-ID: | 20190517162322.kbhf2ult2k245t24@alap3.anarazel.de |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
Hi,
(on postgresql lists please quote emails nicely, and trip irrelevant
pieces)
On 2019-05-17 16:10:52 +0000, Kaleb Akalework wrote:
> 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?
It doesn't have to be the column division - you could just indicate the
desired precision in the divisor. I'd assume that
SELECT BIG_NUM, FLOOR(BIG_NUM/10000000000::numeric(21,10)), BIG_NUM/10000000000::numeric(21,10) from test_table;
would give you precisely the result you waant?
> 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?
That's just because the types here assumed to be bigint (i.e. 64bit
integers):
postgres[22538][1]=# SELECT pg_typeof(3691635539999999999), pg_typeof(10000000000), pg_typeof(3691635539999999999/10000000000), 3691635539999999999/10000000000;
┌───────────┬───────────┬───────────┬───────────┐
│ pg_typeof │ pg_typeof │ pg_typeof │ ?column? │
├───────────┼───────────┼───────────┼───────────┤
│ bigint │ bigint │ bigint │ 369163553 │
└───────────┴───────────┴───────────┴───────────┘
(1 row)
Greetings,
Andres Freund
From | Date | Subject | |
---|---|---|---|
Next Message | David G. Johnston | 2019-05-17 16:23:59 | Re: BUG #15812: Select statement of a very big number, with a division operator seems to round up. |
Previous Message | Kaleb Akalework | 2019-05-17 16:10:52 | RE: BUG #15812: Select statement of a very big number, with a division operator seems to round up. |