From: | Kaleb Akalework <kaleb(dot)akalework(at)asg(dot)com> |
---|---|
To: | Andres Freund <andres(at)anarazel(dot)de>, Alvaro Herrera <alvherre(at)2ndquadrant(dot)com> |
Cc: | "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:36:19 |
Message-ID: | cccebe945f6246c9aee00da3ad4bfc4b@asg.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
Hi,
Thank you Alvaro, Andres and David.
I tried Andres suggestion in his last email and that seems to work as a work around. Please see below.
SELECT BIG_NUM, FLOOR(BIG_NUM /10000000000.0000000000), BIG_NUM/10000000000.0000000000 from test_table
"3691635539999999999" "369163553" "369163553.9999999999"
"3691635530099999999" "369163553" "369163553.0099999999"
"3691635530999999999" "369163553" "369163553.0999999999"
But should this be a bug? Can their a better support of this, instead of having the query writer to know how many decimal numbers to put to get the correct type?
It seems a little awkward and error prone to have to type .0000.... etc?
Thank you all again for the fast response.
Kaleb Akalework
-----Original Message-----
From: Andres Freund <andres(at)anarazel(dot)de>
Sent: Friday, May 17, 2019 12:24 PM
To: Alvaro Herrera <alvherre(at)2ndquadrant(dot)com>
Cc: 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 ***
Hi,
On 2019-05-17 12:02:11 -0400, Alvaro Herrera wrote:
> 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.
Arguably it's less the column's and more the divisor's precision that's the problem. Note that even if big_num were numeric (i.e. without an implied precision) you'd get the OP's results - the precision is not "widened" to the appropriate width for the max precision needed for the division.
Greetings,
Andres Freund
From | Date | Subject | |
---|---|---|---|
Next Message | Dean Rasheed | 2019-05-17 18:02:52 | Re: BUG #15812: Select statement of a very big number, with a division operator seems to round up. |
Previous Message | Andres Freund | 2019-05-17 16:24:15 | Re: BUG #15812: Select statement of a very big number, with a division operator seems to round up. |