Re: Inconsistency between TO_CHAR() and TO_NUMBER()

Am 06.05.2013 16:26, schrieb Tom Lane:
> Patryk Kordylewski <pk(at)fooby(dot)de> writes:
>> Hi,
>> i have the following test-case:
>
>> http://psql.privatepaste.com/b3b431851a
>
> Just for the record: this is a completely unacceptable method of
> submitting a bug report. After a month from now, when that paste has
> expired, nobody looking at the PG archives will have any way to find out
> what you were talking about. Even without the archival consideration,
> you've added an extra step to fetch the test case for anyone reading
> your mail.
>
> Having said that, though, this does look wrong ...
>
> regards, tom lane

Hmpf, you're right. I'm sorry. Here is the test-case:

PostgreSQL 9.2.4 (also tested on 9.1 and 8.2)

### WORKS

SET lc_numeric TO 'en_US.UTF-8';
SET

SELECT
TO_CHAR(123456789.123, 'FM99G999G999G999G999G999G999D000'),
TO_NUMBER(TO_CHAR(123456789.123, 'FM99G999G999G999G999G999G999D000'),
'FM99G999G999G999G999G999G999D000');
to_char | to_number
-----------------+---------------
123,456,789.123 | 123456789.123
(1 row)

### DOES NOT WORK

SET lc_numeric TO 'de_DE.UTF-8';
SET

SELECT
TO_CHAR(123456789.123, 'FM99G999G999G999G999G999G999D000'),
TO_NUMBER(TO_CHAR(123456789.123, 'FM99G999G999G999G999G999G999D000'),
'FM99G999G999G999G999G999G999D000');
to_char | to_number
-----------------+-----------
123.456.789,123 | 123.456
(1 row)

### FORMAT STRING WITHOUT FM PREFIX AND EXACT NUMBER OF DIGITS WORKS

SELECT
TO_CHAR(123456789.123, 'FM99G999G999G999G999G999G999D000'),
TO_NUMBER(TO_CHAR(123456789.123, 'FM99G999G999G999G999G999G999D000'),
'999G999G999D000');
to_char | to_number
-----------------+---------------
123.456.789,123 | 123456789.123
(1 row)

Thank you.

Best regards,
Patryk