Re: to_char(): 'FM' also suppresses *trailing* zeroes

On 3 September 2017 at 19:52, Erwin Brandstetter <brsaweda(at)gmail(dot)com> wrote:

> I think the root of the confusion is that the FM does for date/time
> formatting what the manual says (table 9-25):
>
> FM | prefix fill mode (suppress leading zeroes and padding blanks) |
>> FMMonth
>>
>
> test=# SELECT to_char(timestamp '0910-09-03 01:00:03.04', template)
> test-# FROM (
> test(# VALUES
> test(# ('YYYY-MM-DD HH24:MI:SS.US')
> test(# , ('FMYYYY-MM-DD HH24:MI:SS.US')
> test(# , ('FMYYYY-MM-DD FMHH24:MI:SS.US')
> test(# , ('FMYYYY-FMMM-FMDD FMHH24:FMMI:FMSS.FMUS')
> test(# ) t(template);
> to_char
> ----------------------------
> 0910-09-03 01:00:03.040000
> 910-09-03 01:00:03.040000
> 910-09-03 1:00:03.040000
> 910-9-3 1:0:3.040000
>
> Note how trailing zeros are not suppressed for microseconds. (It would
> seem to make more sense to suppress those, though.)
>
> The same explanation is given for numeric formatting (table 9-27):
>
> FM prefix | fill mode (suppress leading zeroes and padding blanks) |
>> FM9999
>>
>
> But it does not apply there - like you pointed out. The exact behavior
> seems to be:
>
> For the pattern character 0 leading and trailing zeroes are always
> printed, no matter what.
> For the pattern character 9 ...
> without FM modifier
> leading zeros are replaced with padding blanks (before the sign if
> it's there).
> trailing zeros after the decimal point are printed.
> with FM modifier
> leading and trailing zeros are removed (unless overruled by a 0).
> There is additional blank padding for the sign if not printed - also
> removed with FM.
>

This leads to a general problem of this manual page.

There is detailed explanation for "Template Pattern Modifiers for Date/Time
Formatting".
But when it gets to "Template Pattern Modifiers for Numeric Formatting",
the only explanation is this preceding statement:

> Certain modifiers can be applied to any template pattern to alter its
behavior. For example, FM9999 is the 9999 pattern with the FM modifier. Table
9-27
<https://www.postgresql.org/docs/current/static/functions-formatting.html#FUNCTIONS-FORMATTING-NUMERICMOD-TABLE>
shows the modifier patterns for numeric formatting.

Which indicates that above explanation for date/time modifiers would apply
in the same (or analog) way to numeric modifiers. But that's not the case
(at least) for FM. One more notable, undeclared difference, under table-25
it says:

FM suppresses leading zeroes and trailing blanks that would otherwise be
> added to make the output of a pattern be fixed-width. In PostgreSQL, FM
> modifies only the next specification, while in Oracle FM affects all
> subsequent specifications, and repeated FM modifiers toggle fill mode on
> and off.
>

That seems accurate for date/time - see above demo for SELECT
to_char(timestamp '0910-09-03 01:00:03.04', template)
(It's just not clear how "the next specification" is defined exactly.) But
numeric formatting is completely different. The FM modifier can be added
anywhere and any number of times, the result is always the same. And it's
also not (just) about "leading zeroes and trailing blanks" like we worked
out above:

test=# SELECT *, to_char(numeric '1.1', template)
test-# FROM (
test(# VALUES
test(# ('9,999.999')
test(# , ('FM9,999.999')
test(# , ('FM9,999.FM999')
test(# , ('FM9,FM9FM9FM9.FM9FM9FM9')
test(# , ('9,999.FM999') -- !!!
test(# , ('9,999.999FM') -- !!!
test(# , ('9,999.99FM9') -- !!!
test(# ) t(template);
template | to_char
-------------------------+------------
9,999.999 | 1.100
FM9,999.999 | 1.1
FM9,999.FM999 | 1.1
FM9,FM9FM9FM9.FM9FM9FM9 | 1.1
9,999.FM999 | 1.1
9,999.999FM | 1.1
9,999.99FM9 | 1.1

Looks like a bug, a documentation bug or a combination of both.
If it's indeed the intended behavior (?) there should be separate
explanation under table 9-27.

Regards
Erwin