to_number nl_NL.utf8 - Mailing list pgsql-bugs

Dutch locale uses a , as thousand separator, and . as decimal separator.

So to_number( '1.234,55'  ,  '999G999D99')
should return 1234.55

but:

set session lc_numeric to 'nl_NL.utf8';
select
      current_setting('server_version_num') server_num
    , current_setting('lc_numeric') lc_numeric
    , amt
    , to_number(amt, '999G999D99')

from (values
       ('1.234,55')
    ,  ('100,00')
)
    as f(amt)

 server_num | lc_numeric |   amt    | to_number
------------+------------+----------+-----------
 90400      | nl_NL.utf8 | 1.234,55 |    124.55
 90400      | nl_NL.utf8 | 100,00   |     10000
(2 rows)

It easy to work around:  translate( '1.234,55' , ',.', '.' )::numeric ,
but it'd be nice if it could be fixed.

FWIW, behaviour of some older versions:

$ ./lc_numeric_bug.sh
 server_num | lc_numeric |   amt    | to_number
------------+------------+----------+-----------
 90400      | nl_NL.utf8 | 1.234,55 |    124.55
 90400      | nl_NL.utf8 | 100,00   |     10000
(2 rows)

 server_num | lc_numeric |   amt    | to_number
------------+------------+----------+-----------
 90302      | nl_NL.utf8 | 1.234,55 |    124.55
 90302      | nl_NL.utf8 | 100,00   |     10000
(2 rows)

 server_num | lc_numeric |   amt    | to_number
------------+------------+----------+-----------
 90206      | nl_NL.utf8 | 1.234,55 |      1.24
 90206      | nl_NL.utf8 | 100,00   |     10000
(2 rows)


Thanks,

Erik Rijkers