Re: unexpected character used as group separator by to_char

On Tue, 2021-03-09 at 21:28 +0100, Vincent Veyron wrote:
> I'm having a problem with to_char() inserting unexpected characters as group separator.
>
> For the record below, I wish to display 'credit' properly formated in an html form, using to_char(credit, 'FM999G990D00')
>
> SELECT *, to_char(credit, 'FM999G990D00') as yo from tblprospect_balance where id_item = 33;
>
> id_prospect | id_item | date_item | libelle | debit | credit | lettrage | id_facture | yo
> -------------+---------+------------+---------+-------+---------+----------+------------+----------
> 385 | 33 | 2021-03-09 | yo man | 0.00 | 2345.10 | | 8 | 2 345,10
>
> The numbers are properly displayed in the html form. However, updating the record requires
> the user to manually delete the space in '2 345,10', otherwise the last digit is lost.
> Typing numbers including a group separator using the space bar works fine.
>
> I exported the record to a text file and inspected it with emacs in hexadecimal mode :
>
> 3338 3509 3333 0932 3032 312d 3033 2d30 385.33.2021-03-0
> 00000010: 3909 796f 206d 616e 0930 2e30 3009 3233 9.yo man.0.00.23
> 00000020: 3435 2e31 3009 0938 0932 e280 af33 3435 45.10..8.2...345
> 00000030: 2c31 300a ,10.
>
> As you can see, the space in field 'libelle' (yo man) is different from the one in field 'yo' (2...345,10)
>
> What can I do to get a standard space as group separator for my numbers?
>
> #### system information ########
> I use postgresql 11.9 on Debian buster
>
> My settings are :
>
> show lc_numeric;
> lc_numeric
> -------------
> fr_FR.UTF-8

That "space" is UNICODE U+202F ("Narrow No-Break Space"), and that's what your
C library thinks to be the correct group separator for the French language.

You can either replace the character:

SELECT replace(to_char(2345.10, 'FM999G990D00'), E'\u202F', ' ');

or you can choose a different locale that uses a different group separator
(not sure if that exists).

Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com