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

On Fri, May 17, 2019 at 9:11 AM Kaleb Akalework <kaleb(dot)akalework(at)asg(dot)com>
wrote:

> The question is why is the result of the operation dictated by the column
> definition?
>

Because PostgreSQL, and SQL in general, is a typed language and the output
of the division operation is defined to be of the exact same type as its
inputs. Since you are dividing:

numeric(20,0) / bigint

PostgreSQL converts that to:

numeric(20,0) / numeric(20,0) = numeric(20,0)

Then applies the rules for rounding a scaled value to an unscaled one
(i.e., away from half) to the result.

Writing:

numeric / bigint = numeric (same scale/precision as the numeric value)

Basically ends up the same since for these particular values the scale of
the input is 0 and so the scale of the output is also 0 (TBH, I'm a bit
confused writing this in face of third column's result...)

Thus:

numeric(30,10) / bigint = numeric(30,10)

Also...

SELECT 3691635539999999999/10000000000

is

bigint / bigint = bigint (with fractional truncation instead of rounding)

David J.