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