From: Tom Lane <tgl@sss.pgh.pa.us>
To: dqetool@126.com
Cc: pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #19101: Ceil on BIGINT could lost precision in decil function
Date: Sun, 02 Nov 2025 11:35:26 -0500
Message-ID: <427381.1762101326@sss.pgh.pa.us> (raw)
In-Reply-To: <19101-8f44b0e27aa65d40@postgresql.org>
References: <19101-8f44b0e27aa65d40@postgresql.org>
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.
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: tgl@sss.pgh.pa.us, dqetool@126.com, pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #19101: Ceil on BIGINT could lost precision in decil function
In-Reply-To: <427381.1762101326@sss.pgh.pa.us>
* 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