From ce5dcf54b942a469194ae390730f803b3f3fb928 Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Tue, 29 Aug 2017 09:34:21 -0400 Subject: [PATCH] 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 --- doc/src/sgml/func.sgml | 63 ++++++++++++++++++++++++++++++------------ 1 file changed, 46 insertions(+), 17 deletions(-) diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index 28eda97273..641b3b8f4e 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -6351,11 +6351,11 @@ SELECT regexp_match('abc01234xyz', '(?:(.*?)(\d+)(.*)){1,1}'); 9 - value with the specified number of digits + digit position (can be dropped if insignificant) 0 - value with leading zeros + digit position (will not be dropped, even if insignificant) . (period) @@ -6363,7 +6363,7 @@ SELECT regexp_match('abc01234xyz', '(?:(.*?)(\d+)(.*)){1,1}'); , (comma) - group (thousand) separator + group (thousands) separator PR @@ -6421,6 +6421,39 @@ SELECT regexp_match('abc01234xyz', '(?:(.*?)(\d+)(.*)){1,1}'); Usage notes for numeric formatting: + + + 0 specifies a digit position that will always be printed, + even if it contains a leading/trailing zero. 9 also + specifies a digit position, but if it is a leading zero then it will + be replaced by a space, while if it is a trailing zero and fill mode + is specified then it will be deleted. (For to_number(), + these two pattern characters are equivalent.) + + + + + + The pattern characters S, L, D, + and G represent the sign, currency symbol, decimal point, + and thousands separator characters defined by the current locale + (see + and ). The pattern characters period + and comma represent those exact characters, with the meanings of + decimal point and thousands separator, regardless of locale. + + + + + + If no explicit provision is made for a sign + in to_char()'s pattern, one column will be reserved for + the sign, and it will be anchored to (appear just left of) the + number. If S appears just left of some 9's, + it will likewise be anchored to the number. + + + A sign formatted using SG, PL, or @@ -6428,18 +6461,10 @@ SELECT regexp_match('abc01234xyz', '(?:(.*?)(\d+)(.*)){1,1}'); the number; for example, to_char(-12, 'MI9999') produces '-  12' but to_char(-12, 'S9999') produces '  -12'. - The Oracle implementation does not allow the use of + (The Oracle implementation does not allow the use of MI before 9, but rather requires that 9 precede - MI. - - - - - - 9 results in a value with the same number of - digits as there are 9s. If a digit is - not available it outputs a space. + MI.) @@ -6486,8 +6511,8 @@ SELECT regexp_match('abc01234xyz', '(?:(.*?)(\d+)(.*)){1,1}'); Certain modifiers can be applied to any template pattern to alter its - behavior. For example, FM9999 - is the 9999 pattern with the + behavior. For example, FM99.99 + is the 99.99 pattern with the FM modifier. shows the modifier patterns for numeric formatting. @@ -6506,8 +6531,8 @@ SELECT regexp_match('abc01234xyz', '(?:(.*?)(\d+)(.*)){1,1}'); FM prefix - fill mode (suppress leading zeroes and padding blanks) - FM9999 + fill mode (suppress trailing zeroes and padding blanks) + FM99.99 TH suffix @@ -6554,6 +6579,10 @@ SELECT regexp_match('abc01234xyz', '(?:(.*?)(\d+)(.*)){1,1}'); to_char(-0.1, 'FM9.99') '-.1' + + to_char(-0.1, 'FM90.99') + '-0.1' + to_char(0.1, '0.9') ' 0.1' -- 2.40.0