to_number nl_NL.utf8
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
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
On Fri, December 27, 2013 23:14, Erik Rijkers wrote:
Dutch locale uses a , as thousand separator, and . as decimal separator.
Sorry, that's wrong, of course.
Dutch locale uses a . as thousand separator, and , as decimal separator.
I was trying to import values like '1.234,55' into a en_US.utf8 database with
to_number( '1.234,55' , '999G999D99')
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
"Erik Rijkers" <er@xs4all.nl> writes:
I was trying to import values like '1.234,55' into a en_US.utf8 database with
to_number( '1.234,55' , '999G999D99')
I don't think this has much of anything to do with locale.
Observe basically the same behavior without any locale issues:
regression=# select to_number( '1,234.55' , '999,999.99');
to_number
-----------
124.55
(1 row)
regression=# select to_number( '1,234.55' , '99,999.99');
to_number
-----------
134.55
(1 row)
regression=# select to_number( '1,234.55' , '9,999.99');
to_number
-----------
1234.55
(1 row)
I think it's cueing off the number of "9"s to decide which characters mean
what; which would be fine maybe, except it seems to change its mind once
it hits the decimal point. If we're going to believe the input decimal
point position for evaluating digits to its right, probably we should
retroactively adjust what we thought of digits to its left, as well.
Or maybe we shouldn't do either.
Anyway, the short answer for the near future is if you're going to use
to_number then the format ought to match the data.
regards, tom lane
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs