Thread
-
BUG #18977: Unexpected result of function to_char
PG Bug reporting form <noreply@postgresql.org> — 2025-07-03T13:23:12Z
The following bug has been logged on the website: Bug reference: 18977 Logged by: Chi Zhang Email address: 798604270@qq.com PostgreSQL version: 18beta1 Operating system: ubuntu 24.04 with docker Description: hi, 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
-
Re: BUG #18977: Unexpected result of function to_char
Laurenz Albe <laurenz.albe@cybertec.at> — 2025-07-03T14:40:53Z
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
-
Re: BUG #18977: Unexpected result of function to_char
Tom Lane <tgl@sss.pgh.pa.us> — 2025-07-03T15:06:18Z
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 -
Re: BUG #18977: Unexpected result of function to_char
ZhangChi <798604270@qq.com> — 2025-07-03T15:49:27Z
Hi tom 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> 发件时间：2025年7月3日 23:06 收件人：798604270 <798604270@qq.com> 抄送：pgsql-bugs <pgsql-bugs@lists.postgresql.org> 主题：Re: BUG #18977: Unexpected result of function to_char 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