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

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