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
