Обсуждение: to_char(numeric type, text) rounding instead of truncating
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/15/functions-formatting.html Description: I've encountered an issue with to_char(numeric type, text) Apparently, it uses rounding instead of truncation which leads to unpexpected results. This is not reflected in the documentation. My specific example: select to_Char(1235::real/(2::real+1235::real)*100, '99%') The expected output was 99%. The output was ##% - since the number was rounded to 100, there wasn't enough digits in the format. If I add a digit after the decimal point select to_char(1235::real/(2::real+1235::real)*100,'90D0%') The output becomes 99.8% It would be nice to have this behavior explained in the documentation so people get more predictable results.
On Fri, 2023-07-21 at 04:56 +0000, PG Doc comments form wrote: > The following documentation comment has been logged on the website: > > Page: https://www.postgresql.org/docs/15/functions-formatting.html > Description: > > I've encountered an issue with to_char(numeric type, text) > Apparently, it uses rounding instead of truncation which leads to > unpexpected results. This is not reflected in the documentation. > My specific example: > select to_Char(1235::real/(2::real+1235::real)*100, '99%') > The expected output was 99%. The output was ##% - since the number was > rounded to 100, there wasn't enough digits in the format. > If I add a digit after the decimal point > select to_char(1235::real/(2::real+1235::real)*100,'90D0%') > The output becomes 99.8% > > It would be nice to have this behavior explained in the documentation so > people get more predictable results. +1 How about the following: diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index 5a47ce4343..9421ace77e 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -8505,6 +8505,14 @@ SELECT regexp_match('abc01234xyz', '(?:(.*?)(\d+)(.*)){1,1}'); </para> </listitem> + <listitem> + <para> + If the format provides for fewer fractional digits than the number being + formatted has, <function>to_char()</function> will round the number to + the specified number of fractional digits. + </para> + </listitem> + <listitem> <para> The pattern characters <literal>S</literal>, <literal>L</literal>, <literal>D</literal>, Yours, Laurenz Albe
On Wed, Jul 26, 2023 at 07:53:36AM +0200, Laurenz Albe wrote: > On Fri, 2023-07-21 at 04:56 +0000, PG Doc comments form wrote: > > The following documentation comment has been logged on the website: > > > > Page: https://www.postgresql.org/docs/15/functions-formatting.html > > Description: > > > > I've encountered an issue with to_char(numeric type, text) > > Apparently, it uses rounding instead of truncation which leads to > > unpexpected results. This is not reflected in the documentation. > > My specific example: > > select to_Char(1235::real/(2::real+1235::real)*100, '99%') > > The expected output was 99%. The output was ##% - since the number was > > rounded to 100, there wasn't enough digits in the format. > > If I add a digit after the decimal point > > select to_char(1235::real/(2::real+1235::real)*100,'90D0%') > > The output becomes 99.8% > > > > It would be nice to have this behavior explained in the documentation so > > people get more predictable results. > > +1 > > How about the following: I slightly modified your patch and applied it back to PG 11 since all supported Postgres versions have the same behavior. Thanks. -- Bruce Momjian <bruce@momjian.us> https://momjian.us EDB https://enterprisedb.com Only you can decide what is important to you.
On Wed, 2023-09-06 at 16:53 -0400, Bruce Momjian wrote: > I slightly modified your patch and applied it back to PG 11 since all > supported Postgres versions have the same behavior. Thanks. Thanks for picking it up. Yours, Laurenz Albe