Thread: problem with to_char

Hi!

I think I have not understood some elementary things of the to_char function
in pgsql7.1.2. I only get back a mask instead of a formatted string.

I tried the following in psql:
SPS=> select to_char(45145, '9.999,99');
  to_char
-----------
  #.###,##
(1 row)

I also tried a "normal" query in the form:
    select col1,col2,to_char(col3, '9.999,99') from test;
with col3 as numeric values. I also only get a mask in the 3rd col.

What am I doing wrongly?

Kind regards,
Carsten Grewe

> I think I have not understood some elementary things of the to_char function
> in pgsql7.1.2. I only get back a mask instead of a formatted string.
> I tried the following in psql:
> SPS=> select to_char(45145, '9.999,99');
>   to_char
> -----------
>   #.###,##
> (1 row)

The number will not fit into the field you have defined, so it is filled
with hash marks to indicate overflow.

If your locale is not set up to recognize "european style" numbers, then
in this case you have defined a formatting string which allows only
numbers less than 10. If you are set up to recognize european formatting
(I'm not familiar with this for to_char(); see the docs) then you have
defined a formatting string with allows only numbers less than 9999,
which still won't hold the number you are asking it to format for you.

Try to_char(45145, '99,999.99') and to_char(45145, '99.999,99'); one of
them will work.

hth

                     - Thomas

On Tue, Jan 29, 2002 at 01:13:44PM +0000, Thomas Lockhart wrote:
> > I think I have not understood some elementary things of the to_char function
> > in pgsql7.1.2. I only get back a mask instead of a formatted string.
> > I tried the following in psql:
> > SPS=> select to_char(45145, '9.999,99');
> >   to_char
> > -----------
> >   #.###,##
> > (1 row)
>
> The number will not fit into the field you have defined, so it is filled
> with hash marks to indicate overflow.

 Right.

> If your locale is not set up to recognize "european style" numbers, then
> in this case you have defined a formatting string which allows only
> numbers less than 10. If you are set up to recognize european formatting
> (I'm not familiar with this for to_char(); see the docs) then you have
> defined a formatting string with allows only numbers less than 9999,
> which still won't hold the number you are asking it to format for you.
>
> Try to_char(45145, '99,999.99') and to_char(45145, '99.999,99'); one of
> them will work.

 Right is to_char(45145, '99,999.99'). The ',' is default (means US)
 group separator and the '.' is decimal point. If you want to use
 european (means based on locale) formatting you must use 'D' as
 deciamal point and 'G' as group separator.

 For example:

 US style:

test=# select to_char(45145, '99,999.99');
  to_char
------------
  45,145.00
(1 row)


 European (locale) style:

test=# select to_char(45145, '99G999D99');
  to_char
------------
  45 145,00
(1 row)


 The number of '9' front decimal point must be greater or same as is number
of digits in input.

test=# select to_char(45145, '9999');
 to_char
---------
  ####
(1 row)

test=# select to_char(45145, '999999999');
  to_char
------------
      45145
(1 row)

test=# select to_char(45145, '099999999');
  to_char
------------
  000045145
(1 row)

test=# select to_char(45145, '099G999G999D999G999');
       to_char
----------------------
  000 045 145,000 000
(1 row)

        Karel

--
 Karel Zak  <zakkr@zf.jcu.cz>
 http://home.zf.jcu.cz/~zakkr/

 C, PostgreSQL, PHP, WWW, http://docs.linux.cz, http://mape.jcu.cz