Re: to_char with locale decimal separator

On Mon, Jul 29, 2013 at 3:12 PM, Ingmar Brouns <swingi(at)gmail(dot)com> wrote:
> On Mon, Jul 29, 2013 at 1:24 PM, Ingmar Brouns <swingi(at)gmail(dot)com> wrote:
>> Hi,
>>
>> I need to convert some numerical values to text using the decimal
>> separator that corresponds to the current locale. However, I do
>> not want to lose information by padding with zero decimals or
>> truncating zero decimals. So I basically want a text cast that
>> also replaces the dot by a comma. I've looked at the to_char
>> function and the formatting patterns, but when using those I
>> either add or truncate zero decimals.
>>
>>
>> # show lc_numeric;
>> lc_numeric
>> ------------
>> nl_NL.utf8
>> (1 row)
>>
>> # select 1.500::text;
>> text
>> -------
>> 1.500
>> (1 row)
>>
>> # select to_char(1.500, '999999D99999999');
>> to_char
>> ------------------
>> 1,50000000
>> (1 row)
>>
>> # select to_char(1.500, 'FM999999D99999999');
>> to_char
>> ---------
>> 1,5
>> (1 row)
>>
>
> Maybe its important to add that the nr of decimals in the values
> is variable. I could of course adjust the pattern to work for
> 1.500, but I'm looking for a solution that will work with an
> arbitrary numerical value and that's a little more elegant than
> casting to text, and then replacing the dot by a comma.
>

anyone? Giving a locale corresponding textual representation
of a numerical value keeping the exact nr of decimal digits
must be a fairly common use case. Would it be an idea to
implement a to_char function that does not take a formatting
pattern and has this behaviour?

>
>>
>> I would like to have '1,500' as the output, what is the best way
>> to achieve this?
>>
>> Thanks in advance,
>>
>> Ingmar