pg.ddx.io pgsql-docs@postgresql.org mailing list archive
help / color / mirror / Atom feedto_char(numeric type, text) rounding instead of truncating
4+ messages / 3 participants
[nested] [flat]
* to_char(numeric type, text) rounding instead of truncating
@ 2023-07-21 04:56 PG Doc comments form <noreply@postgresql.org>
2023-07-26 05:53 ` Re: to_char(numeric type, text) rounding instead of truncating Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 1 reply; 4+ messages in thread
From: PG Doc comments form @ 2023-07-21 04:56 UTC (permalink / raw)
To: pgsql-docs@lists.postgresql.org; +Cc: barsikdacat@gmail.com
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.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: to_char(numeric type, text) rounding instead of truncating
2023-07-21 04:56 to_char(numeric type, text) rounding instead of truncating PG Doc comments form <noreply@postgresql.org>
@ 2023-07-26 05:53 ` Laurenz Albe <laurenz.albe@cybertec.at>
2023-09-06 20:53 ` Re: to_char(numeric type, text) rounding instead of truncating Bruce Momjian <bruce@momjian.us>
0 siblings, 1 reply; 4+ messages in thread
From: Laurenz Albe @ 2023-07-26 05:53 UTC (permalink / raw)
To: barsikdacat@gmail.com; pgsql-docs@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: to_char(numeric type, text) rounding instead of truncating
2023-07-21 04:56 to_char(numeric type, text) rounding instead of truncating PG Doc comments form <noreply@postgresql.org>
2023-07-26 05:53 ` Re: to_char(numeric type, text) rounding instead of truncating Laurenz Albe <laurenz.albe@cybertec.at>
@ 2023-09-06 20:53 ` Bruce Momjian <bruce@momjian.us>
2023-09-06 20:58 ` Re: to_char(numeric type, text) rounding instead of truncating Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 1 reply; 4+ messages in thread
From: Bruce Momjian @ 2023-09-06 20:53 UTC (permalink / raw)
To: Laurenz Albe <laurenz.albe@cybertec.at>; +Cc: barsikdacat@gmail.com; pgsql-docs@lists.postgresql.org
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.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: to_char(numeric type, text) rounding instead of truncating
2023-07-21 04:56 to_char(numeric type, text) rounding instead of truncating PG Doc comments form <noreply@postgresql.org>
2023-07-26 05:53 ` Re: to_char(numeric type, text) rounding instead of truncating Laurenz Albe <laurenz.albe@cybertec.at>
2023-09-06 20:53 ` Re: to_char(numeric type, text) rounding instead of truncating Bruce Momjian <bruce@momjian.us>
@ 2023-09-06 20:58 ` Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 0 replies; 4+ messages in thread
From: Laurenz Albe @ 2023-09-06 20:58 UTC (permalink / raw)
To: Bruce Momjian <bruce@momjian.us>; +Cc: barsikdacat@gmail.com; pgsql-docs@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2023-09-06 20:58 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-07-21 04:56 to_char(numeric type, text) rounding instead of truncating PG Doc comments form <noreply@postgresql.org>
2023-07-26 05:53 ` Laurenz Albe <laurenz.albe@cybertec.at>
2023-09-06 20:53 ` Bruce Momjian <bruce@momjian.us>
2023-09-06 20:58 ` Laurenz Albe <laurenz.albe@cybertec.at>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox