Skip site navigation (1) Skip section navigation (2)

wrong thousand/group separator with to_char on certain locales...

From: "Netto" <rcnetto13-php(at)yahoo(dot)com>
To: pgsql-bugs(at)postgresql(dot)org
Subject: wrong thousand/group separator with to_char on certain locales...
Date: 2004-11-10 13:35:39
Message-ID: cmt5c8$1f7u$ (view raw, whole thread or download thread mbox)
Lists: pgsql-bugs
Hey all,...
I've faced a "bogus" situation in postgreSQL while using "to_char" function
with locale resources that was described before at, and I was
able to identify what the real problem is...

                        POSTGRESQL BUG REPORT TEMPLATE

Your name  : Ranulfo Netto
Your email address : rcnetto (at) yahoo (dot) com

System Configuration
  Architecture : Intel Pentium
  Operating System : Suse (9.1) Linux 2.6.5-7.108-default
  PostgreSQL version :   PostgreSQL-7.4.5
  Compiler used : gcc version 3.3.3 (SuSE Linux)

Please enter a FULL description of your problem:
When using the function "to_char", with locale support, to format a given
number to a string, the wrong thousand (group) separator is used by
It happens only for certain locales which don't have a defined thousand
separator for numeric values into their settings.

Please describe a way to repeat the problem.   Please try to provide a
concise reproducible example, if at all possible:
- The initialized database must have locale support.
- Try the following:
example #1:
testdb=# set lc_numeric TO 'de_DE';
testdb=# select to_char(10975.23,'999G999G999G990D00');
(1 row)
- This first example does the right thing because 'de_DE' locale has . (dot)
as its thousand separator for numeric values, so the correct value was

example #2:
testdb=# set lc_numeric TO 'pt_BR';
testdb=# select to_char(10975.23,'999G999G999G990D00');
(1 row)
- Now, as brazilian portuguese (pt_BR) locale does not have thousand
separator for numeric values, postgresql uses (at its own) the comma as
The same happens to pt_PT, de_AT and many others.

If you know how this problem might be fixed, list the solution below:
Browsing the source code, I was able to identify the code that defines that

Annotation of pgsql/src/backend/utils/adt/formatting.c, revision 1.81:
1.35      momjian  3549:        /*
1.1       momjian  3550:         * Number thousands separator
1.8       momjian  3551:         */
1.1       momjian  3552:        if (lconv->thousands_sep &&
                   3553:            Np->L_thousands_sep =
1.8       momjian  3554:        else
1.1       momjian  3555:            Np->L_thousands_sep = ",";

Well, this code asks for a thousand separator. If it does not exists, then
the comma is given.
I believe this behavior should be different, cause there are several locales
without thousand separator, and that should be respected.
I mean, the arbitrary values should be given only if any kind of locale info
were found. It can't just mix the locale info with default values... What a
mess, isn't it? :)
Note that this "bogus" can occur to any lconv property when it's not
defined, since the others proterties are tested the same way.

There's another issue to be discussed about that, even I guess this is not
the right place to do so... In Oracle, it's possible to define what are the
numeric characters we want to_char function use when formatting
(NLS_NUMERIC_CHARACTERS = '.,') and since the comments at formatting.c file,
reforce the connection between postgreSQL and Oracle ("inspired by the
Oracle TO_CHAR() / TO_DATE() / TO_NUMBER() routines."), maybe postgreSQL
could have an option such like this to override locale info.

Contact me for more info about this hole thing...
That's it...

pgsql-bugs by date

Next:From: Stefano TazziDate: 2004-11-10 15:57:37
Subject: PostgreSQL 8 Installed on Windows
Previous:From: Riccardo G. FacchiniDate: 2004-11-10 13:21:05
Subject: bugreport 7.4.5

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group