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
