From: Laurenz Albe <laurenz.albe@cybertec.at>
To: dqetool@126.com
To: pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #19101: Ceil on BIGINT could lost precision in decil function
Date: Sun, 02 Nov 2025 17:31:29 +0100
Message-ID: <2581d77962f88322686e4cd1a326d98823002e5e.camel@cybertec.at> (raw)
In-Reply-To: <19101-8f44b0e27aa65d40@postgresql.org>
References: <19101-8f44b0e27aa65d40@postgresql.org>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: laurenz.albe@cybertec.at, dqetool@126.com, pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #19101: Ceil on BIGINT could lost precision in decil function
In-Reply-To: <2581d77962f88322686e4cd1a326d98823002e5e.camel@cybertec.at>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox