"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