Horton

Thread

Commits

  1. Improve docs about numeric formatting patterns (to_char/to_number).

  1. Failing example for to_number()

    Erwin Brandstetter <brsaweda@gmail.com> — 2017-08-21T12:36:27Z

    The manual suggests here:

https://www.postgresql.org/docs/current/static/functions-formatting.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')


Regards
Erwin Brandstetter

  2. Re: Failing example for to_number()

    David G. Johnston <david.g.johnston@gmail.com> — 2017-08-21T14:30:15Z

    On Mon, Aug 21, 2017 at 5:36 AM, Erwin Brandstetter <brsaweda@gmail.com>
wrote:

> The manual suggests here:
>
> https://www.postgresql.org/docs/current/static/functions-
> formatting.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.
​

  3. Re: Failing example for to_number()

    Erwin Brandstetter <brsaweda@gmail.com> — 2017-08-21T14:55:52Z

    On 21 August 2017 at 16:30, David G. Johnston <david.g.johnston@gmail.com>
wrote:

> On Mon, Aug 21, 2017 at 5:36 AM, Erwin Brandstetter <brsaweda@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

  4. Re: Failing example for to_number()

    David G. Johnston <david.g.johnston@gmail.com> — 2017-08-21T15:30:40Z

    On Mon, Aug 21, 2017 at 7:55 AM, Erwin Brandstetter <brsaweda@gmail.com>
wrote:

> 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.
>
>
​Table 9-26 on that page says "(uses locale)" for D and G, the user is left
to deduce that since comma and period do not have that qualifier that they
are not locale dependent.​


> I guess there should be some more explanation.
>

I don't really agree but I wouldn't discourage someone from submitting a
patch.

David J.
​

  5. Re: Failing example for to_number()

    Tom Lane <tgl@sss.pgh.pa.us> — 2017-08-28T20:53:57Z

    Erwin Brandstetter <brsaweda@gmail.com> writes:
> On 21 August 2017 at 16:30, David G. Johnston <david.g.johnston@gmail.com>
> wrote:
>> On Mon, Aug 21, 2017 at 5:36 AM, Erwin Brandstetter <brsaweda@gmail.com>
>> wrote:
>>> The example fails for locales where the comma (',') does not happen to be
>>> the group separator and the dot ('.') is not the decimal point.

>> 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.

> I guess there should be some more explanation.

Yeah.  How about adding something like this to the "Usage notes for
numeric formatting" between tables 9-26 and 9-27:

	* The pattern characters S, L, D, and G represent the sign,
	currency symbol, decimal point, and thousands separator characters
	defined by the current locale (see lc_monetary and lc_numeric).
	The pattern characters period and comma represent those exact
	characters, with the meanings of decimal point and thousands
	separator, regardless of locale.

			regards, tom lane

  6. Re: Failing example for to_number()

    Erwin Brandstetter <brsaweda@gmail.com> — 2017-09-03T17:13:34Z

    On 28 August 2017 at 22:53, Tom Lane <tgl@sss.pgh.pa.us> wrote:

> Erwin Brandstetter <brsaweda@gmail.com> writes:
> > On 21 August 2017 at 16:30, David G. Johnston <
> david.g.johnston@gmail.com>
> > wrote:
> >> On Mon, Aug 21, 2017 at 5:36 AM, Erwin Brandstetter <brsaweda@gmail.com
> >
> >> wrote:
> >>> The example fails for locales where the comma (',') does not happen to
> be
> >>> the group separator and the dot ('.') is not the decimal point.
>
> >> 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.
>
> > I guess there should be some more explanation.
>
> Yeah.  How about adding something like this to the "Usage notes for
> numeric formatting" between tables 9-26 and 9-27:
>
>         * The pattern characters S, L, D, and G represent the sign,
>         currency symbol, decimal point, and thousands separator characters
>         defined by the current locale (see lc_monetary and lc_numeric).
>         The pattern characters period and comma represent those exact
>         characters, with the meanings of decimal point and thousands
>         separator, regardless of locale.
>
>
That should help to clear up some confusion, especially for to_number(). I
suggest some minor modifications:

        * The pattern characters S, L, D, and G represent the sign,
        currency symbol, decimal point, and group separator characters
        defined by the current locale (see lc_monetary and lc_numeric).
        The pattern characters period (.) and comma (,) represent decimal
point and group
        separator, regardless of locale.


Regards
Erwin Brandstetter