Re: Inconsistency between TO_CHAR() and TO_NUMBER()

On Fri, May 10, 2013 at 11:49:18AM -0400, Tom Lane wrote:
> Patryk Kordylewski <pk@fooby.de> writes:
> > 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)
>
> I looked into this, and find that the reason it misbehaves is that
> NUM_numpart_from_char() will treat a '.' as being a decimal point
> *without any regard to locale considerations*.  So even if we have
> a locale-dependent format string and a locale that says '.' is a
> thousands separator, it does the wrong thing.
>
> It's a bit surprising nobody's complained of this before.
>
> I propose the attached patch.  I'm slightly worried though about whether
> this might break any existing applications that are (incorrectly)
> depending on a D format specifier being able to match '.' regardless of
> locale.  Perhaps we should only apply this to HEAD and not back-patch?

I never trust to_char() changes for backpatching.  ;-)

--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + It's impossible for everything to be true. +