BUG #18977: Unexpected result of function to_char

On Thu, 2025-07-03 at 13:23 +0000, PG Bug reporting form wrote:

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
---------
-#.
```
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

This is the same as bug report #18976.
For user guidance, please write to the pgsql-general mailing list.
This list is for bug reports.

Yours,
Laurenz Albe

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

Hi tom&nbsp;lane,

Thank you very much for your explanation.

I am trying to detecting issues in prepare statement and I can understant this now.

I sincerely apologize for reporting a false positive.

原始邮件

发件人：Tom Lane <tgl@sss.pgh.pa.us&gt;
发件时间：2025年7月3日 23:06
收件人：798604270 <798604270@qq.com&gt;
抄送：pgsql-bugs <pgsql-bugs@lists.postgresql.org&gt;
主题：Re: BUG #18977: Unexpected result of function to_char

PG&nbsp;Bug&nbsp;reporting&nbsp;form&nbsp;<noreply@postgresql.org&gt;&nbsp;writes:
&gt;&nbsp;the&nbsp;following&nbsp;two&nbsp;queries&nbsp;are&nbsp;equivalent&nbsp;but&nbsp;return&nbsp;different&nbsp;results:
&gt;&nbsp;```
&gt;&nbsp;SELECT&nbsp;((to_char(-1E30,&nbsp;'0.9930824')));
&gt;&nbsp;&nbsp;&nbsp;to_char
&gt;&nbsp;------------
&gt;&nbsp;&nbsp;-#.##3#824
&gt;&nbsp;(1&nbsp;row)
&gt;&nbsp;PREPARE&nbsp;prepare_query&nbsp;(float8)&nbsp;AS&nbsp;SELECT&nbsp;((to_char($1,&nbsp;'0.9930824')));
&gt;&nbsp;EXECUTE&nbsp;prepare_query(-1E30::float8);
&gt;&nbsp;&nbsp;to_char
&gt;&nbsp;---------
&gt;&nbsp;&nbsp;-#.
&gt;&nbsp;```

They&nbsp;are&nbsp;not&nbsp;equivalent:&nbsp;the&nbsp;float8&nbsp;and&nbsp;numeric&nbsp;variants&nbsp;of&nbsp;to_char
behave&nbsp;somewhat&nbsp;differently,&nbsp;because&nbsp;of&nbsp;the&nbsp;need&nbsp;to&nbsp;round&nbsp;off&nbsp;float8
values&nbsp;to&nbsp;no&nbsp;more&nbsp;than&nbsp;about&nbsp;15&nbsp;decimal&nbsp;digits.&nbsp;&nbsp;(If&nbsp;we&nbsp;failed&nbsp;to&nbsp;do
so,&nbsp;we'd&nbsp;print&nbsp;useless&nbsp;noise&nbsp;digits.)&nbsp;&nbsp;In&nbsp;this&nbsp;case&nbsp;float8_to_char
decides&nbsp;that&nbsp;it&nbsp;can't&nbsp;print&nbsp;any&nbsp;digits&nbsp;beyond&nbsp;the&nbsp;decimal&nbsp;point.

&gt;&nbsp;furthermore,&nbsp;it&nbsp;seems&nbsp;the&nbsp;second&nbsp;argument&nbsp;of&nbsp;to_chat&nbsp;is&nbsp;formatted,&nbsp;but
&gt;&nbsp;according&nbsp;to&nbsp;the&nbsp;document&nbsp;in
&gt;&nbsp;https://www.postgresql.org/docs/current/functions-formatting.html,&amp;nbsp;it&amp;nbsp;should
&gt;&nbsp;be&nbsp;the&nbsp;first&nbsp;argument&nbsp;to&nbsp;be&nbsp;formatted

It&nbsp;is&nbsp;the&nbsp;first&nbsp;argument&nbsp;that&nbsp;is&nbsp;formatted.&nbsp;&nbsp;You&nbsp;are&nbsp;passing&nbsp;a&nbsp;garbage
value&nbsp;of&nbsp;the&nbsp;format&nbsp;string,&nbsp;and&nbsp;unsurprisingly&nbsp;getting&nbsp;a&nbsp;garbage
result.&nbsp;&nbsp;(Only&nbsp;the&nbsp;0's,&nbsp;9's,&nbsp;and&nbsp;decimal&nbsp;point&nbsp;act&nbsp;as&nbsp;format&nbsp;characters.)
You'd&nbsp;get&nbsp;better&nbsp;results&nbsp;with&nbsp;a&nbsp;format&nbsp;that&nbsp;is&nbsp;wide&nbsp;enough&nbsp;to&nbsp;hold
the&nbsp;value,&nbsp;say

regression=#&nbsp;SELECT&nbsp;to_char(-1E30::numeric,&nbsp;'0.9999999eeee');
&nbsp;&nbsp;&nbsp;&nbsp;to_char&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
----------------
&nbsp;-1.0000000e+30
(1&nbsp;row)

regression=#&nbsp;SELECT&nbsp;to_char(-1E30::float8,&nbsp;'0.9999999eeee');
&nbsp;&nbsp;&nbsp;&nbsp;to_char&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
----------------
&nbsp;-1.0000000e+30
(1&nbsp;row)

regards,&nbsp;tom&nbsp;lane