Thread

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

Laurenz Albe <laurenz.albe@cybertec.at> — 2025-11-02T16:31:29Z

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