pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #19101: Ceil on BIGINT could lost precision in decil function
4+ messages / 4 participants
[nested] [flat]
* BUG #19101: Ceil on BIGINT could lost precision in decil function
@ 2025-11-02 15:16 PG Bug reporting form <noreply@postgresql.org>
0 siblings, 2 replies; 4+ messages in thread
From: PG Bug reporting form @ 2025-11-02 15:16 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: dqetool@126.com
The following bug has been logged on the website:
Bug reference: 19101
Logged by: Jason Smith
Email address: dqetool@126.com
PostgreSQL version: 18.0
Operating system: Ubuntu 22.04
Description:
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. In this case, calling
numeric_ceil function may be proper, and I try the following case.
```sql
CREATE TABLE t1 (c1 DECIMAL(20,0));
INSERT INTO t1 VALUES (4854233034440979799);
-- numeric_ceil
SELECT ceil(c1) FROM t1; -- {4854233034440979799}
```
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: BUG #19101: Ceil on BIGINT could lost precision in decil function
@ 2025-11-02 16:31 Laurenz Albe <laurenz.albe@cybertec.at>
parent: PG Bug reporting form <noreply@postgresql.org>
1 sibling, 0 replies; 4+ messages in thread
From: Laurenz Albe @ 2025-11-02 16:31 UTC (permalink / raw)
To: dqetool@126.com; pgsql-bugs@lists.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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: BUG #19101: Ceil on BIGINT could lost precision in decil function
@ 2025-11-02 16:35 Tom Lane <tgl@sss.pgh.pa.us>
parent: PG Bug reporting form <noreply@postgresql.org>
1 sibling, 1 reply; 4+ messages in thread
From: Tom Lane @ 2025-11-02 16:35 UTC (permalink / raw)
To: dqetool@126.com; +Cc: pgsql-bugs@lists.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.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re:Re: BUG #19101: Ceil on BIGINT could lost precision in decil function
@ 2025-11-03 02:48 dqetool <dqetool@126.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 4+ messages in thread
From: dqetool @ 2025-11-03 02:48 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-bugs@lists.postgresql.org
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.
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2025-11-03 02:48 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-11-02 15:16 BUG #19101: Ceil on BIGINT could lost precision in decil function PG Bug reporting form <noreply@postgresql.org>
2025-11-02 16:31 ` Laurenz Albe <laurenz.albe@cybertec.at>
2025-11-02 16:35 ` Tom Lane <tgl@sss.pgh.pa.us>
2025-11-03 02:48 ` dqetool <dqetool@126.com>
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