Re: [DOCS] to_char(): 'FM' also suppresses *trailing* zeroes
От | Erwin Brandstetter |
---|---|
Тема | Re: [DOCS] to_char(): 'FM' also suppresses *trailing* zeroes |
Дата | |
Msg-id | CAGHENJ7rY3J4+bBmrv4_UWJkdnObRdGrxEBw8c9kOJqyfBGMmQ@mail.gmail.com обсуждение исходный текст |
Ответ на | Re: [DOCS] to_char(): 'FM' also suppresses *trailing* zeroes (Tom Lane <tgl@sss.pgh.pa.us>) |
Ответы |
Re: [DOCS] to_char(): 'FM' also suppresses *trailing* zeroes
(Erwin Brandstetter <brsaweda@gmail.com>)
|
Список | pgsql-docs |
On 28 August 2017 at 22:37, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Erwin Brandstetter <brsaweda@gmail.com> writes:
> In Table 9-27. "Template Pattern Modifiers for Numeric Formatting" it says:
> FM | prefix fill mode (suppress leading zeroes and padding blanks) | FM9999
> In fact, 'FM' also suppresses *trailing* zeroes after the comma. To fix,
> this might be changed to:
> suppress insignificant zeroes and padding blanks
Not necessarily. A bit of experimentation says that it also matters
whether you use "0" or "9" as the format character:
So it appears to me that the bit you point out is flat out backwards;
what FM actually suppresses is trailing zeroes not leading zeroes.
I'm tempted to propose that in table 9-26, we need to write
9 digit position (can be dropped if insignificant)
0 digit position (cannot be dropped, even if insignificant)
and then in 9-27 say
FM fill mode: suppress trailing zeroes and padding spaces
Also, in between those two tables, I see
* 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.
This seems outright wrong per the above examples, and in any case is not
very useful since it doesn't explain the difference from "0". Perhaps
rewrite as
* 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.
Leading zeros are removed completely for 9 with FM. And without FM they are actually replaced with spaces before the sign. Maybe:
* 0 specifies a digit position that will always be printed, even as insignificant zero.
9 also specifies a digit position, but leading zeros are replaced with spaces before the optional sign. And if fill mode is specified (FM modifier) insignificant leading and trailing zeros are removed completely.
I created a more comprehensive test matrix for to_char(numeric, text) to aid in a quick review of the FM modifier:
CREATE TEMP TABLE t(t_id int, template text);
INSERT INTO t(t_id, template)
VALUES
( 1, '00.00')
, ( 2, '09.90')
, ( 3, '90.09')
, ( 4, '99.99')
, ( 5, '090.090')
, ( 6, '909.909')
, (11, 'FM00.00')
, (12, 'FM09.90')
, (13, 'FM90.09')
, (14, 'FM99.99')
, (15, 'FM090.090')
, (16, 'FM909.909');
CREATE TEMP TABLE v (v_id int, val numeric);
INSERT INTO v
VALUES
(1, -0.1 )
, (2, 0.12 )
, (3, -0.123)
, (4, -1.1 )
, (5, 12.12 )
, (6, -123.123);
SELECT val, template, to_char(val, template)
FROM v, t
ORDER BY v_id, t_id;
INSERT INTO t(t_id, template)
VALUES
( 1, '00.00')
, ( 2, '09.90')
, ( 3, '90.09')
, ( 4, '99.99')
, ( 5, '090.090')
, ( 6, '909.909')
, (11, 'FM00.00')
, (12, 'FM09.90')
, (13, 'FM90.09')
, (14, 'FM99.99')
, (15, 'FM090.090')
, (16, 'FM909.909');
CREATE TEMP TABLE v (v_id int, val numeric);
INSERT INTO v
VALUES
(1, -0.1 )
, (2, 0.12 )
, (3, -0.123)
, (4, -1.1 )
, (5, 12.12 )
, (6, -123.123);
SELECT val, template, to_char(val, template)
FROM v, t
ORDER BY v_id, t_id;
To test online:
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
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.
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).
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.
Regards
Erwin
В списке pgsql-docs по дате отправления:
Следующее
От: Erwin BrandstetterДата:
Сообщение: Re: [DOCS] to_char(): 'FM' also suppresses *trailing* zeroes