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

On Sun, 2025-11-02 at 15:16 +0000, PG Bug reporting form wrote:
> PostgreSQL version: 18.0
>
> 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}
> ```
> The original number is expected to return.

This is not a bug. There are two ceil() functions:

List of functions
Schema │ Name │ Result data type │ Argument data types │ Type
════════════╪══════╪══════════════════╪═════════════════════╪══════
pg_catalog │ ceil │ double precision │ double precision │ func
pg_catalog │ ceil │ numeric │ numeric │ func

There are implicit casts from "bigint" to both "numeric" and "double precision":

List of casts
Source type │ Target type │ Function │ Implicit?
══════════════════╪══════════════════╪══════════╪═══════════════
...
bigint │ double precision │ float8 │ yes
...
bigint │ numeric │ numeric │ yes

There are two preferred numeric data types, and "numeric" is none of them:

SELECT typname FROM pg_type WHERE typcategory = 'N' AND typispreferred;

typname
═════════
oid
float8 (which is the same as "double precision")

Consequently, rule 4 d of the type conversion rules for function calls
(https://www.postgresql.org/docs/current/typeconv-func.html)
decrees that the "bigint" be case to "double precision", which explains
the rounding errors.

Use an explicit type cast:

SELECT ceil(c1::numeric) FROM t1;

Yours,
Laurenz Albe