Thread: 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

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')

"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