On 21 August 2017 at 16:30, David G. Johnston <david.g.johns...@gmail.com> wrote:
> On Mon, Aug 21, 2017 at 5:36 AM, Erwin Brandstetter <brsaw...@gmail.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
