From: dqetool <dqetool@126.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-bugs@lists.postgresql.org
Subject: Re:Re: BUG #19101: Ceil on BIGINT could lost precision in decil function
Date: Mon, 3 Nov 2025 10:48:34 +0800 (CST)
Message-ID: <43e5b082.29f0.19a479dcb70.Coremail.dqetool@126.com> (raw)
In-Reply-To: <427381.1762101326@sss.pgh.pa.us>
References: <19101-8f44b0e27aa65d40@postgresql.org>
<427381.1762101326@sss.pgh.pa.us>
Thanks for the quick response. I try to add an explicit type conversion to avoid this problem.
At 2025-11-03 00:35:26, "Tom Lane" <tgl@sss.pgh.pa.us> wrote:
>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: dqetool@126.com, tgl@sss.pgh.pa.us, pgsql-bugs@lists.postgresql.org
Subject: Re:Re: BUG #19101: Ceil on BIGINT could lost precision in decil function
In-Reply-To: <43e5b082.29f0.19a479dcb70.Coremail.dqetool@126.com>
* 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