From: | Erwin Brandstetter <brsaweda(at)gmail(dot)com> |
---|---|
To: | "David G(dot) Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com> |
Cc: | "pgsql-docs(at)postgresql(dot)org" <pgsql-docs(at)postgresql(dot)org> |
Subject: | Re: Failing example for to_number() |
Date: | 2017-08-21 14:55:52 |
Message-ID: | CAGHENJ45Lt40ab8rjLo-OjxuXoLZduJTaBi5iPk-z259nkFsRQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-docs |
On 21 August 2017 at 16:30, David G. Johnston <david(dot)g(dot)johnston(at)gmail(dot)com>
wrote:
> On Mon, Aug 21, 2017 at 5:36 AM, Erwin Brandstetter <brsaweda(at)gmail(dot)com>
> wrote:
>
>> The manual suggests here:
>>
>> https://www.postgresql.org/docs/current/static/functions-for
>> matting.html#FUNCTIONS-FORMATTING-TABLE
>>
>> > to_number(text, text) | numeric convert string to numeric | to_number
>> ('12,454.8-', '99G999D9S')
>>
>> The example fails for locales where the comma (',') does not happen to be
>> the group separator and the dot ('.') is not the decimal point.
>>
>> The example is incorrectly assuming en_US locale. It must instead work
>> locale-agnostic. Replace:
>>
>> to_number ('12,454.8-', '99G999D9S')
>>
>> with:
>>
>> to_number ('12,454.8-', '99,999.9S')
>>
>>
> I can see the appeal of demonstrating the locale-dependent G and D
> symbols so that a user becoming familiar with the feature knows that they
> have options other than just writing out the comma and period literally.
> If one wants to try the example in a language other than in which the
> example was written they should modify it so that the literal number being
> parsed in written in conformance to the locale definition for the language
> you are using.
>
> David J.
>
>
Well, there is still 'S' in the pattern to pointing to non-literal pattern
symbols (without making the example fail). I see your point, though.
On top of this (separate issue?) ',' and '.' are ***not*** interpreted
according to the current LC_NUMERIC setting. I.e.: even with (example)
Austrian locale, ',' still is interpreted as group separator and '.' still
is the decimal point.
It seems those just follow standard meaning without locale (C locale) -
while 'D' and 'G' pattern symbols enforce locale specific format. It took
me a while to untangle this and I suspect many people will fail to
understand it.
I guess there should be some more explanation.
I stumbled across this while working on this answer on dba.stackexchange.com
:
https://dba.stackexchange.com/questions/183964/is-it-possible-to-insert-un-formatted-data-for-money-data-type-in-postgresql/183975#183975
Regards
Erwin
From | Date | Subject | |
---|---|---|---|
Next Message | David G. Johnston | 2017-08-21 15:30:40 | Re: Failing example for to_number() |
Previous Message | David G. Johnston | 2017-08-21 14:30:15 | Re: Failing example for to_number() |