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

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
expectdecimal 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
thedecimal 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