Skip to content

Commit d4ab780

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 2f4ffae commit d4ab780

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
@@ -5775,19 +5775,19 @@ SELECT regexp_matches('abc01234xyz', '(?:(.*?)(\d+)(.*)){1,1}');
57755775
<tbody>
57765776
<row>
57775777
<entry><literal>9</literal></entry>
5778-
<entry>value with the specified number of digits</entry>
5778+
<entry>digit position (can be dropped if insignificant)</entry>
57795779
</row>
57805780
<row>
57815781
<entry><literal>0</literal></entry>
5782-
<entry>value with leading zeros</entry>
5782+
<entry>digit position (will not be dropped, even if insignificant)</entry>
57835783
</row>
57845784
<row>
57855785
<entry><literal>.</literal> (period)</entry>
57865786
<entry>decimal point</entry>
57875787
</row>
57885788
<row>
57895789
<entry><literal>,</literal> (comma)</entry>
5790-
<entry>group (thousand) separator</entry>
5790+
<entry>group (thousands) separator</entry>
57915791
</row>
57925792
<row>
57935793
<entry><literal>PR</literal></entry>
@@ -5845,25 +5845,50 @@ SELECT regexp_matches('abc01234xyz', '(?:(.*?)(\d+)(.*)){1,1}');
58455845
Usage notes for numeric formatting:
58465846

58475847
<itemizedlist>
5848+
<listitem>
5849+
<para>
5850+
<literal>0</> specifies a digit position that will always be printed,
5851+
even if it contains a leading/trailing zero. <literal>9</> also
5852+
specifies a digit position, but if it is a leading zero then it will
5853+
be replaced by a space, while if it is a trailing zero and fill mode
5854+
is specified then it will be deleted. (For <function>to_number()</>,
5855+
these two pattern characters are equivalent.)
5856+
</para>
5857+
</listitem>
5858+
5859+
<listitem>
5860+
<para>
5861+
The pattern characters <literal>S</>, <literal>L</>, <literal>D</>,
5862+
and <literal>G</> represent the sign, currency symbol, decimal point,
5863+
and thousands separator characters defined by the current locale
5864+
(see <xref linkend="guc-lc-monetary">
5865+
and <xref linkend="guc-lc-numeric">). The pattern characters period
5866+
and comma represent those exact characters, with the meanings of
5867+
decimal point and thousands separator, regardless of locale.
5868+
</para>
5869+
</listitem>
5870+
5871+
<listitem>
5872+
<para>
5873+
If no explicit provision is made for a sign
5874+
in <function>to_char()</>'s pattern, one column will be reserved for
5875+
the sign, and it will be anchored to (appear just left of) the
5876+
number. If <literal>S</> appears just left of some <literal>9</>'s,
5877+
it will likewise be anchored to the number.
5878+
</para>
5879+
</listitem>
5880+
58485881
<listitem>
58495882
<para>
58505883
A sign formatted using <literal>SG</literal>, <literal>PL</literal>, or
58515884
<literal>MI</literal> is not anchored to
58525885
the number; for example,
58535886
<literal>to_char(-12, 'MI9999')</literal> produces <literal>'-&nbsp;&nbsp;12'</literal>
58545887
but <literal>to_char(-12, 'S9999')</literal> produces <literal>'&nbsp;&nbsp;-12'</literal>.
5855-
The Oracle implementation does not allow the use of
5888+
(The Oracle implementation does not allow the use of
58565889
<literal>MI</literal> before <literal>9</literal>, but rather
58575890
requires that <literal>9</literal> precede
5858-
<literal>MI</literal>.
5859-
</para>
5860-
</listitem>
5861-
5862-
<listitem>
5863-
<para>
5864-
<literal>9</literal> results in a value with the same number of
5865-
digits as there are <literal>9</literal>s. If a digit is
5866-
not available it outputs a space.
5891+
<literal>MI</literal>.)
58675892
</para>
58685893
</listitem>
58695894

@@ -5908,8 +5933,8 @@ SELECT regexp_matches('abc01234xyz', '(?:(.*?)(\d+)(.*)){1,1}');
59085933

59095934
<para>
59105935
Certain modifiers can be applied to any template pattern to alter its
5911-
behavior. For example, <literal>FM9999</literal>
5912-
is the <literal>9999</literal> pattern with the
5936+
behavior. For example, <literal>FM99.99</literal>
5937+
is the <literal>99.99</literal> pattern with the
59135938
<literal>FM</literal> modifier.
59145939
<xref linkend="functions-formatting-numericmod-table"> shows the
59155940
modifier patterns for numeric formatting.
@@ -5928,8 +5953,8 @@ SELECT regexp_matches('abc01234xyz', '(?:(.*?)(\d+)(.*)){1,1}');
59285953
<tbody>
59295954
<row>
59305955
<entry><literal>FM</literal> prefix</entry>
5931-
<entry>fill mode (suppress leading zeroes and padding blanks)</entry>
5932-
<entry><literal>FM9999</literal></entry>
5956+
<entry>fill mode (suppress trailing zeroes and padding blanks)</entry>
5957+
<entry><literal>FM99.99</literal></entry>
59335958
</row>
59345959
<row>
59355960
<entry><literal>TH</literal> suffix</entry>
@@ -5976,6 +6001,10 @@ SELECT regexp_matches('abc01234xyz', '(?:(.*?)(\d+)(.*)){1,1}');
59766001
<entry><literal>to_char(-0.1, 'FM9.99')</literal></entry>
59776002
<entry><literal>'-.1'</literal></entry>
59786003
</row>
6004+
<row>
6005+
<entry><literal>to_char(-0.1, 'FM90.99')</literal></entry>
6006+
<entry><literal>'-0.1'</literal></entry>
6007+
</row>
59796008
<row>
59806009
<entry><literal>to_char(0.1, '0.9')</literal></entry>
59816010
<entry><literal>'&nbsp;0.1'</literal></entry>

0 commit comments

Comments
 (0)