Skip to content

Commit 669bef9

Browse files
tglsfdctglsfdc
committed
Improve docs about numeric formatting patterns (to_char/to_number).
The explanation about "0" versus "9" format characters was confusing and arguably wrong; the discussion of sign handling wasn't very good either. Notably, while it's accurate to say that "FM" strips leading zeroes in date/time values, what it really does with numeric values is to strip *trailing* zeroes, and then only if you wrote "9" rather than "0". Per gripes from Erwin Brandstetter. Discussion: https://postgr.es/m/CAGHENJ7jgRbTn6nf48xNZ=FHgL2WQ4X8mYsUAU57f-vq8PubEw@mail.gmail.com Discussion: https://postgr.es/m/CAGHENJ45ymd=GOCu1vwV9u7GmCR80_5tW0fP9C_gJKbruGMHvQ@mail.gmail.com
1 parent 93067c5 commit 669bef9

File tree

1 file changed

+46
-17
lines changed

1 file changed

+46
-17
lines changed

‎doc/src/sgml/func.sgml

Lines changed: 46 additions & 17 deletions
Original file line numberDiff line numberDiff line change
@@ -5991,19 +5991,19 @@ SELECT regexp_matches('abc01234xyz', '(?:(.*?)(\d+)(.*)){1,1}');
59915991
<tbody>
59925992
<row>
59935993
<entry><literal>9</literal></entry>
5994-
<entry>value with the specified number of digits</entry>
5994+
<entry>digit position (can be dropped if insignificant)</entry>
59955995
</row>
59965996
<row>
59975997
<entry><literal>0</literal></entry>
5998-
<entry>value with leading zeros</entry>
5998+
<entry>digit position (will not be dropped, even if insignificant)</entry>
59995999
</row>
60006000
<row>
60016001
<entry><literal>.</literal> (period)</entry>
60026002
<entry>decimal point</entry>
60036003
</row>
60046004
<row>
60056005
<entry><literal>,</literal> (comma)</entry>
6006-
<entry>group (thousand) separator</entry>
6006+
<entry>group (thousands) separator</entry>
60076007
</row>
60086008
<row>
60096009
<entry><literal>PR</literal></entry>
@@ -6061,25 +6061,50 @@ SELECT regexp_matches('abc01234xyz', '(?:(.*?)(\d+)(.*)){1,1}');
60616061
Usage notes for numeric formatting:
60626062

60636063
<itemizedlist>
6064+
<listitem>
6065+
<para>
6066+
<literal>0</> specifies a digit position that will always be printed,
6067+
even if it contains a leading/trailing zero. <literal>9</> also
6068+
specifies a digit position, but if it is a leading zero then it will
6069+
be replaced by a space, while if it is a trailing zero and fill mode
6070+
is specified then it will be deleted. (For <function>to_number()</>,
6071+
these two pattern characters are equivalent.)
6072+
</para>
6073+
</listitem>
6074+
6075+
<listitem>
6076+
<para>
6077+
The pattern characters <literal>S</>, <literal>L</>, <literal>D</>,
6078+
and <literal>G</> represent the sign, currency symbol, decimal point,
6079+
and thousands separator characters defined by the current locale
6080+
(see <xref linkend="guc-lc-monetary">
6081+
and <xref linkend="guc-lc-numeric">). The pattern characters period
6082+
and comma represent those exact characters, with the meanings of
6083+
decimal point and thousands separator, regardless of locale.
6084+
</para>
6085+
</listitem>
6086+
6087+
<listitem>
6088+
<para>
6089+
If no explicit provision is made for a sign
6090+
in <function>to_char()</>'s pattern, one column will be reserved for
6091+
the sign, and it will be anchored to (appear just left of) the
6092+
number. If <literal>S</> appears just left of some <literal>9</>'s,
6093+
it will likewise be anchored to the number.
6094+
</para>
6095+
</listitem>
6096+
60646097
<listitem>
60656098
<para>
60666099
A sign formatted using <literal>SG</literal>, <literal>PL</literal>, or
60676100
<literal>MI</literal> is not anchored to
60686101
the number; for example,
60696102
<literal>to_char(-12, 'MI9999')</literal> produces <literal>'-&nbsp;&nbsp;12'</literal>
60706103
but <literal>to_char(-12, 'S9999')</literal> produces <literal>'&nbsp;&nbsp;-12'</literal>.
6071-
The Oracle implementation does not allow the use of
6104+
(The Oracle implementation does not allow the use of
60726105
<literal>MI</literal> before <literal>9</literal>, but rather
60736106
requires that <literal>9</literal> precede
6074-
<literal>MI</literal>.
6075-
</para>
6076-
</listitem>
6077-
6078-
<listitem>
6079-
<para>
6080-
<literal>9</literal> results in a value with the same number of
6081-
digits as there are <literal>9</literal>s. If a digit is
6082-
not available it outputs a space.
6107+
<literal>MI</literal>.)
60836108
</para>
60846109
</listitem>
60856110

@@ -6124,8 +6149,8 @@ SELECT regexp_matches('abc01234xyz', '(?:(.*?)(\d+)(.*)){1,1}');
61246149

61256150
<para>
61266151
Certain modifiers can be applied to any template pattern to alter its
6127-
behavior. For example, <literal>FM9999</literal>
6128-
is the <literal>9999</literal> pattern with the
6152+
behavior. For example, <literal>FM99.99</literal>
6153+
is the <literal>99.99</literal> pattern with the
61296154
<literal>FM</literal> modifier.
61306155
<xref linkend="functions-formatting-numericmod-table"> shows the
61316156
modifier patterns for numeric formatting.
@@ -6144,8 +6169,8 @@ SELECT regexp_matches('abc01234xyz', '(?:(.*?)(\d+)(.*)){1,1}');
61446169
<tbody>
61456170
<row>
61466171
<entry><literal>FM</literal> prefix</entry>
6147-
<entry>fill mode (suppress leading zeroes and padding blanks)</entry>
6148-
<entry><literal>FM9999</literal></entry>
6172+
<entry>fill mode (suppress trailing zeroes and padding blanks)</entry>
6173+
<entry><literal>FM99.99</literal></entry>
61496174
</row>
61506175
<row>
61516176
<entry><literal>TH</literal> suffix</entry>
@@ -6192,6 +6217,10 @@ SELECT regexp_matches('abc01234xyz', '(?:(.*?)(\d+)(.*)){1,1}');
61926217
<entry><literal>to_char(-0.1, 'FM9.99')</literal></entry>
61936218
<entry><literal>'-.1'</literal></entry>
61946219
</row>
6220+
<row>
6221+
<entry><literal>to_char(-0.1, 'FM90.99')</literal></entry>
6222+
<entry><literal>'-0.1'</literal></entry>
6223+
</row>
61956224
<row>
61966225
<entry><literal>to_char(0.1, '0.9')</literal></entry>
61976226
<entry><literal>'&nbsp;0.1'</literal></entry>

0 commit comments

Comments
 (0)