From: Tom Lane <tgl@sss.pgh.pa.us>
To: 798604270@qq.com
Cc: pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #18977: Unexpected result of function to_char
Date: Thu, 03 Jul 2025 11:06:18 -0400
Message-ID: <3911294.1751555178@sss.pgh.pa.us> (raw)
In-Reply-To: <18977-b301735f4ecad02f@postgresql.org>
References: <18977-b301735f4ecad02f@postgresql.org>
PG Bug reporting form <noreply@postgresql.org> writes:
> the following two queries are equivalent but return different results:
> ```
> SELECT ((to_char(-1E30, '0.9930824')));
> to_char
> ------------
> -#.##3#824
> (1 row)
> PREPARE prepare_query (float8) AS SELECT ((to_char($1, '0.9930824')));
> EXECUTE prepare_query(-1E30::float8);
> to_char
> ---------
> -#.
> ```
They are not equivalent: the float8 and numeric variants of to_char
behave somewhat differently, because of the need to round off float8
values to no more than about 15 decimal digits. (If we failed to do
so, we'd print useless noise digits.) In this case float8_to_char
decides that it can't print any digits beyond the decimal point.
> furthermore, it seems the second argument of to_chat is formatted, but
> according to the document in
> https://www.postgresql.org/docs/current/functions-formatting.html, it should
> be the first argument to be formatted
It is the first argument that is formatted. You are passing a garbage
value of the format string, and unsurprisingly getting a garbage
result. (Only the 0's, 9's, and decimal point act as format characters.)
You'd get better results with a format that is wide enough to hold
the value, say
regression=# SELECT to_char(-1E30::numeric, '0.9999999eeee');
to_char
----------------
-1.0000000e+30
(1 row)
regression=# SELECT to_char(-1E30::float8, '0.9999999eeee');
to_char
----------------
-1.0000000e+30
(1 row)
regards, tom lane
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, 798604270@qq.com, pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #18977: Unexpected result of function to_char
In-Reply-To: <3911294.1751555178@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