Thread: BUG #18977: Unexpected result of function to_char
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
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