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

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 https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services