Failing example for to

pg.ddx.io  pgsql-docs@postgresql.org mailing list archive  
help / color / mirror / Atom feed

Failing example for to_number()
6+ messages / 3 participants
[nested] [flat]

Commits from this thread:
  2017-08-29 ce5dcf54b9 dbc7a7d920 5c81c881f7 669bef911c e19e12be48 d4ab7808bc 09ec0eb7a6  Improve docs about numeric formatting patterns (to_char/to_number).

* Failing example for to_number()
@ 2017-08-21 12:36 Erwin Brandstetter <brsaweda@gmail.com>
  2017-08-21 14:30 ` Re: Failing example for to_number() David G. Johnston <david.g.johnston@gmail.com>
  0 siblings, 1 reply; 6+ messages in thread

From: Erwin Brandstetter @ 2017-08-21 12:36 UTC (permalink / raw)
  To: pgsql-docs

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

^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: Failing example for to_number()
  2017-08-21 12:36 Failing example for to_number() Erwin Brandstetter <brsaweda@gmail.com>
@ 2017-08-21 14:30 ` David G. Johnston <david.g.johnston@gmail.com>
  2017-08-21 14:55   ` Re: Failing example for to_number() Erwin Brandstetter <brsaweda@gmail.com>
  0 siblings, 1 reply; 6+ messages in thread

From: David G. Johnston @ 2017-08-21 14:30 UTC (permalink / raw)
  To: Erwin Brandstetter <brsaweda@gmail.com>; +Cc: pgsql-docs

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.


^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: Failing example for to_number()
  2017-08-21 12:36 Failing example for to_number() Erwin Brandstetter <brsaweda@gmail.com>
  2017-08-21 14:30 ` Re: Failing example for to_number() David G. Johnston <david.g.johnston@gmail.com>
@ 2017-08-21 14:55   ` Erwin Brandstetter <brsaweda@gmail.com>
  2017-08-21 15:30     ` Re: Failing example for to_number() David G. Johnston <david.g.johnston@gmail.com>
  2017-08-28 20:53     ` Re: Failing example for to_number() Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 2 replies; 6+ messages in thread

From: Erwin Brandstetter @ 2017-08-21 14:55 UTC (permalink / raw)
  To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: pgsql-docs

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

Regards
Erwin

^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: Failing example for to_number()
  2017-08-21 12:36 Failing example for to_number() Erwin Brandstetter <brsaweda@gmail.com>
  2017-08-21 14:30 ` Re: Failing example for to_number() David G. Johnston <david.g.johnston@gmail.com>
  2017-08-21 14:55   ` Re: Failing example for to_number() Erwin Brandstetter <brsaweda@gmail.com>
@ 2017-08-21 15:30     ` David G. Johnston <david.g.johnston@gmail.com>
  1 sibling, 0 replies; 6+ messages in thread

From: David G. Johnston @ 2017-08-21 15:30 UTC (permalink / raw)
  To: Erwin Brandstetter <brsaweda@gmail.com>; +Cc: pgsql-docs

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.


^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: Failing example for to_number()
  2017-08-21 12:36 Failing example for to_number() Erwin Brandstetter <brsaweda@gmail.com>
  2017-08-21 14:30 ` Re: Failing example for to_number() David G. Johnston <david.g.johnston@gmail.com>
  2017-08-21 14:55   ` Re: Failing example for to_number() Erwin Brandstetter <brsaweda@gmail.com>
@ 2017-08-28 20:53     ` Tom Lane <tgl@sss.pgh.pa.us>
  2017-09-03 17:13       ` Re: Failing example for to_number() Erwin Brandstetter <brsaweda@gmail.com>
  1 sibling, 1 reply; 6+ messages in thread

From: Tom Lane @ 2017-08-28 20:53 UTC (permalink / raw)
  To: Erwin Brandstetter <brsaweda@gmail.com>; +Cc: David G. Johnston <david.g.johnston@gmail.com>; pgsql-docs

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


-- 
Sent via pgsql-docs mailing list (pgsql-docs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-docs



^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: Failing example for to_number()
  2017-08-21 12:36 Failing example for to_number() Erwin Brandstetter <brsaweda@gmail.com>
  2017-08-21 14:30 ` Re: Failing example for to_number() David G. Johnston <david.g.johnston@gmail.com>
  2017-08-21 14:55   ` Re: Failing example for to_number() Erwin Brandstetter <brsaweda@gmail.com>
  2017-08-28 20:53     ` Re: Failing example for to_number() Tom Lane <tgl@sss.pgh.pa.us>
@ 2017-09-03 17:13       ` Erwin Brandstetter <brsaweda@gmail.com>
  0 siblings, 0 replies; 6+ messages in thread

From: Erwin Brandstetter @ 2017-09-03 17:13 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: David G. Johnston <david.g.johnston@gmail.com>; pgsql-docs

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

^ permalink  raw  reply  [nested|flat] 6+ messages in thread

end of thread, other threads:[~2017-09-03 17:13 UTC | newest]

Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2017-08-21 12:36 Failing example for to_number() Erwin Brandstetter <brsaweda@gmail.com>
2017-08-21 14:30 ` David G. Johnston <david.g.johnston@gmail.com>
2017-08-21 14:55   ` Erwin Brandstetter <brsaweda@gmail.com>
2017-08-21 15:30     ` David G. Johnston <david.g.johnston@gmail.com>
2017-08-28 20:53     ` Tom Lane <tgl@sss.pgh.pa.us>
2017-09-03 17:13       ` Erwin Brandstetter <brsaweda@gmail.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox