Thread: Why length(to_char(1::integer, '9')) = 2 ?

2011/3/9 Dmitriy Igrishin <dmitigr@gmail.com>:
> Hey all,
>
> dmitigr=> select to_char(1, '9');
>  to_char
> ---------
>   1
>
> dmitigr=> select length(to_char(1, '9'));
>  length
> --------
>       2
>
> Why to_char() includes preceding blank space in the result ?

it is compatibility with Oracle?

Regards

Pavel

>
> --
> // Dmitriy.
>
>
>

On 09/03/2011 13:15, Pavel Stehule wrote:
> 2011/3/9 Dmitriy Igrishin<dmitigr@gmail.com>:
>> Hey all,
>>
>> dmitigr=>  select to_char(1, '9');
>>   to_char
>> ---------
>>    1
>>
>> dmitigr=>  select length(to_char(1, '9'));
>>   length
>> --------
>>        2
>>
>> Why to_char() includes preceding blank space in the result ?
>
> it is compatibility with Oracle?

I've often wondered too why there is padding by default in certain uses
of to_char().... it's a bit of a PITA sometimes. :-)

Ray.


--
Raymond O'Donnell :: Galway :: Ireland
rod@iol.ie

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On 03/09/2011 03:12 PM, Dmitriy Igrishin wrote:

> Hey all,
>
> dmitigr=> select to_char(1, '9');
> to_char
> ---------
> 1
>
> dmitigr=> select length(to_char(1, '9'));
> length
> --------
> 2
>
> Why to_char() includes preceding blank space in the result ?
>
> --
> // Dmitriy.
>
>
I don't know why, but to work around it use:
select to_char(1, 'FM9');
select length(to_char(1, 'FM9'));

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.10 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/

iQEcBAEBAgAGBQJNd3/JAAoJEHr2Gm0ENObOsUMH/ApWyfc5c5A56m1pAP7raIEd
dmY0/aocCCnQbariREZIGSJPrmcWDKnNe3yNLjV2Y3+EY+eaicxy2GPTVamOrfqN
tYQ/ImH3IkrzQk1bfRX+lnUJQGEmMi8ClzAatKUIifGJwMuj7y1xUl/VBTP0lBvI
GuQQaElNkpGaPRTJZlorrtqEBgWmiyBT07gK02IST9xFsUPnrF0niNlqcaphF2Ga
kKgFfVJ8u/C3KbwowVPh5GYZHgIM1T8x6SPzpcsnFVrIGN+avnuvdEInxomCZDNN
FLuRBEPK9NFTG6rdIyrtfy5C6HVm/q7rO1alW0hjuszou1t2gBCOkmXtva9V5gY=
=pGTI
-----END PGP SIGNATURE-----

2011/3/9 Dmitriy Igrishin <dmitigr@gmail.com>:
>
>
> 2011/3/9 Pavel Stehule <pavel.stehule@gmail.com>
>>
>> 2011/3/9 Dmitriy Igrishin <dmitigr@gmail.com>:
>> > Hey all,
>> >
>> > dmitigr=> select to_char(1, '9');
>> >  to_char
>> > ---------
>> >   1
>> >
>> > dmitigr=> select length(to_char(1, '9'));
>> >  length
>> > --------
>> >       2
>> >
>> > Why to_char() includes preceding blank space in the result ?
>>
>> it is compatibility with Oracle?
>
> Do you mean the case of MI ?
> So, is this leading space reserved for a sign of number by default ?

yes

pavel=# select '>' || to_char(-1,'9') || '<';
 ?column?
──────────
 >-1<
(1 row)

regards

Pavel

>>
>> Regards
>>
>> Pavel
>>
>> >
>> > --
>> > // Dmitriy.
>> >
>> >
>> >
>
>
>
> --
> // Dmitriy.
>
>
>

On Wednesday, March 09, 2011 5:34:41 am Dmitriy Igrishin wrote:

>
> But I am missing something or there is a documentation inaccuracy:
> http://www.postgresql.org/docs/9.0/static/functions-formatting.html#FUNCTIO
> NS-FORMATTING-NUMERICMOD-TABLEsays: fill mode (suppress padding blanks and
> zeroes)
>
> Test:
> dmitigr=> select to_char(12,'FM0009');
>  to_char
> ---------
>  0012
>
> dmitigr=> select length(to_char(12,'FM0009'));
>  length
> --------
>       4
>
> So, FM suppresses only padding blanks not zeroes...
>
> Any comments?
>

test(5432)aklaver=>select to_char(12,'9999');
 to_char
---------
    12

test(5432)aklaver=>select to_char(12,'FM9999');
 to_char
---------
 12

It is a little confusing, but you asked for the 0 in your specification so they
are not considered padding.

Look at the examples in the table listed below to get an idea of what I am
talking about.
http://www.postgresql.org/docs/9.0/static/functions-formatting.html
Table 9-25


--
Adrian Klaver
adrian.klaver@gmail.com

On 03/09/2011 09:59 AM, Dmitriy Igrishin wrote:
>
>
> 2011/3/9 Adrian Klaver <adrian.klaver@gmail.com
> <mailto:adrian.klaver@gmail.com>>
>
>     On Wednesday, March 09, 2011 5:34:41 am Dmitriy Igrishin wrote:
>
>      >
>      > But I am missing something or there is a documentation inaccuracy:
>      >
>     http://www.postgresql.org/docs/9.0/static/functions-formatting.html#FUNCTIO
>      > NS-FORMATTING-NUMERICMOD-TABLEsays: fill mode (suppress padding
>     blanks and
>      > zeroes)
>      >
>      > Test:
>      > dmitigr=> select to_char(12,'FM0009');
>      >  to_char
>      > ---------
>      >  0012
>      >
>      > dmitigr=> select length(to_char(12,'FM0009'));
>      >  length
>      > --------
>      >       4
>      >
>      > So, FM suppresses only padding blanks not zeroes...
>      >
>      > Any comments?
>      >
>
>     test(5432)aklaver=>select to_char(12,'9999');
>       to_char
>     ---------
>         12
>
>     test(5432)aklaver=>select to_char(12,'FM9999');
>       to_char
>     ---------
>       12
>
>     It is a little confusing, but you asked for the 0 in your
>     specification so they
>     are not considered padding.
>
>     Look at the examples in the table listed below to get an idea of
>     what I am
>     talking about.
>     http://www.postgresql.org/docs/9.0/static/functions-formatting.html
>     Table 9-25
>
> Yes, I see, thanks!
>
> I just talking about phrase "fill mode (suppress padding blanks and zeroes)"
> in the documentation should be rephrased to "fill mode (suppress padding
> blanks)".

To get technical it means suppress unspecified padding O's. See below
for example.

>
> Or I misunderstood what is "padding zeroes" without explicitly
> specification "0" pattern in the format format template...

This combination from the example table shows that:

to_char(-0.1, 'FM9.99')    '-.1'
to_char(0.1, '0.9')    ' 0.1'

The 0 in 0.1 is not strictly needed, so if you use FM it will be suppressed.


>
>
>
>     --
>     Adrian Klaver
>     adrian.klaver@gmail.com <mailto:adrian.klaver@gmail.com>
>
>
>
>
> --
> // Dmitriy.
>
>


--
Adrian Klaver
adrian.klaver@gmail.com

Dmitriy Igrishin wrote:
> dmitigr=> SELECT '>'||to_char(0.1, '0.99999')||'<' AS v;
>      v
> ------------
>  > 0.10000<
>
> dmitigr=> SELECT '>'||to_char(0.1, 'FM0.99999')||'<' AS v;
>    v
> -------
>  >0.1<
>
> So, padding zeroes suppressed by FM is a rest of the value.

Any documentation changes suggested?

--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + It's impossible for everything to be true. +

Dmitriy Igrishin wrote:
> 2011/3/10 Bruce Momjian <bruce@momjian.us>
>
> > Dmitriy Igrishin wrote:
> > > dmitigr=> SELECT '>'||to_char(0.1, '0.99999')||'<' AS v;
> > >      v
> > > ------------
> > >  > 0.10000<
> > >
> > > dmitigr=> SELECT '>'||to_char(0.1, 'FM0.99999')||'<' AS v;
> > >    v
> > > -------
> > >  >0.1<
> > >
> > > So, padding zeroes suppressed by FM is a rest of the value.
> >
> > Any documentation changes suggested?
> >
> I propose to replace "fill mode (suppress padding blanks and zeroes)"
> located here
> http://www.postgresql.org/docs/9.0/static/functions-formatting.html#FUNCTIONS-FORMATTING-NUMERICMOD-TABLE
> with
> "fill mode (suppress padding blanks and trailing zeroes)".

Thanks.  Attached patch applied.

--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + It's impossible for everything to be true. +
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index f746ed6..da45970 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -5446,7 +5446,7 @@ SELECT SUBSTRING('XY1234Z', 'Y*?([0-9]{1,3})');
       <tbody>
        <row>
         <entry><literal>FM</literal> prefix</entry>
-        <entry>fill mode (suppress padding blanks and zeroes)</entry>
+        <entry>fill mode (suppress padding blanks and trailing zeroes)</entry>
         <entry><literal>FMMonth</literal></entry>
        </row>
        <row>
@@ -5812,7 +5812,7 @@ SELECT SUBSTRING('XY1234Z', 'Y*?([0-9]{1,3})');
       <tbody>
        <row>
         <entry><literal>FM</literal> prefix</entry>
-        <entry>fill mode (suppress padding blanks and zeroes)</entry>
+        <entry>fill mode (suppress padding blanks and trailing zeroes)</entry>
         <entry><literal>FM9999</literal></entry>
        </row>
        <row>