Re:Re: BUG #19101: Ceil on BIGINT could lost precision in decil function

Thanks for the quick response. I try to add an explicit type conversion to avoid this problem.
At 2025-11-03 00:35:26, "Tom Lane" <tgl@sss.pgh.pa.us> wrote:
>PG Bug reporting form <noreply@postgresql.org> writes:
>> I try to store a large number in `BIGINT` and run  `ceil(c1)` command.
>> However, the result lost some precision due to calling `decil` function.
>> ```sql
>> CREATE TABLE t1 (c1 BIGINT);
>> INSERT INTO t1 VALUES (4854233034440979799);
>> -- dceil
>> SELECT ceil(c1) FROM t1; -- {4.854233034440979e+18}
>> ```
>
>This is not a bug.  There are two versions of ceil() and you're
>invoking the wrong one.  You'd need to explicitly cast the
>argument to numeric if you want ceil(numeric) to be used.
>
>In this context it's a bit unfortunate that the parser's type
>preference rules [1] prefer float8 to numeric.  But we're pretty
>much stuck with that behavior because (a) the SQL standard
>says so [2], and (b) even if it didn't, we have a couple of
>decades of history to be backwards compatible with.
>
>            regards, tom lane
>
>[1] https://www.postgresql.org/docs/current/typeconv.html
>
>[2] Well, what it really says is that expressions that mix
>exact and inexact numeric types produce inexact results.
>We interpret that as meaning that float8 is the preferred
>type in the numeric category, so it wins ambiguous cases.