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

Started by Dmitriy Igrishinabout 15 years ago15 messagesgeneral

Dmitriy Igrishin

dmitigr@gmail.com

about 15 years ago

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.

pavel.stehule@gmail.com

about 15 years ago

In reply to: Dmitriy Igrishin (#1)

Re: 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

Show quoted text

--
// Dmitriy.

Raymond O'Donnell

rod@iol.ie

about 15 years ago

In reply to: Pavel Stehule (#2)

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

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

sim@compulab.co.il

about 15 years ago

In reply to: Dmitriy Igrishin (#1)

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

-----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-----

Dmitriy Igrishin

dmitigr@gmail.com

about 15 years ago

In reply to: Pavel Stehule (#2)

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

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 ?

Regards

Pavel

--
// Dmitriy.

--
// Dmitriy.

pavel.stehule@gmail.com

about 15 years ago

In reply to: Dmitriy Igrishin (#5)

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

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

Show quoted text

Regards

Pavel

--
// Dmitriy.

--
// Dmitriy.

Dmitriy Igrishin

dmitigr@gmail.com

about 15 years ago

In reply to: Pavel Stehule (#6)

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

2011/3/9 Pavel Stehule <pavel.stehule@gmail.com>

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)

Aha! Thanks.

regards

Pavel

Regards

Pavel

--
// Dmitriy.

--
// Dmitriy.

--
// Dmitriy.

Dmitriy Igrishin

dmitigr@gmail.com

about 15 years ago

In reply to: Sim Zacks (#4)

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

2011/3/9 Sim Zacks <sim@compulab.co.il>

-----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'));

Thanks!

But I am missing something or there is a documentation inaccuracy:
http://www.postgresql.org/docs/9.0/static/functions-formatting.html#FUNCTIONS-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?

-----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-----

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

--
// Dmitriy.

adrian.klaver@aklaver.com

about 15 years ago

In reply to: Dmitriy Igrishin (#8)

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

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

Dmitriy Igrishin

dmitigr@gmail.com

about 15 years ago

In reply to: Adrian Klaver (#9)

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

2011/3/9 Adrian Klaver <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)".

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

--
Adrian Klaver
adrian.klaver@gmail.com

--
// Dmitriy.

adrian.klaver@aklaver.com

about 15 years ago

In reply to: Dmitriy Igrishin (#10)

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

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

dmitigr@gmail.com

about 15 years ago

In reply to: Adrian Klaver (#11)

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

2011/3/9 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.

Ahh, I guess I understand (thanks to you examples).
Lets look at the test:

dmitigr=> SELECT '>'||to_char(-0.1, 'FM9.99')||'<' AS v;
v
-------

-.1<

dmitigr=> SELECT '>'||to_char(0.1, '0.9')||'<' AS v;
v
--------

0.1<

dmitigr=> SELECT '>'||to_char(0.1, 'FM0.9')||'<' AS v;
v
-------

0.1<

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.

Thank you very much!

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

--
// Dmitriy.

--
Adrian Klaver
adrian.klaver@gmail.com

--
// Dmitriy.

bruce@momjian.us

about 15 years ago

In reply to: Dmitriy Igrishin (#12)

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

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

dmitigr@gmail.com

about 15 years ago

In reply to: Bruce Momjian (#13)

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

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)".

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

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

--
// Dmitriy.

bruce@momjian.us

about 15 years ago

In reply to: Dmitriy Igrishin (#14)

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

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. +