Localization trouble

On Jul 5, 2007, at 19:10 , Chris Travers wrote:

I have been trying to use set lc_numeric = various country codes
(for example es_EC), but I am not able to get the format to change
from 1.00 to 1,00.
Any hints as to what I could be doing wrong?

Does this correspond to what you're seeing?

test=# CREATE TABLE lc_examples (a_money money not null, a_numeric
numeric not null);
CREATE TABLE
test=# INSERT INTO lc_examples (a_money, a_numeric) VALUES ('1.32',
-1.32);
INSERT 0 1
test=# CREATE VIEW lc_examples_view AS
SELECT a_money
, a_numeric
, to_char(a_numeric, '999D99S') as a_formatted_numeric
FROM lc_examples;
CREATE VIEW
test=# SELECT * FROM lc_examples_view;
a_money | a_numeric | a_formatted_numeric
---------+-----------+---------------------
$1.32 | -1.32 | 1.32-
(1 row)

test=# SHOW lc_monetary;
lc_monetary
-------------
C
(1 row)

test=# SHOW lc_numeric;
lc_numeric
------------
C
(1 row)

test=# SELECT * FROM lc_examples_view;
a_money | a_numeric | a_formatted_numeric
---------+-----------+---------------------
$1.32 | -1.32 | 1.32-
(1 row)

test=# SET lc_monetary TO 'es_ES';
SET
test=# SET lc_numeric TO 'es_ES';
SET
test=# SELECT * FROM lc_examples_view;
a_money | a_numeric | a_formatted_numeric
---------+-----------+---------------------
Eu1,32 | -1.32 | 1,32-
(1 row)

I don't believe you'll see numbers *as numbers* displayed with the
formatting you desire unless you somehow tell your client (e.g.,
psql) which locale you want to use. I haven't figured out how to do
this yet, though.

Michael Glaesemann
grzm seespotcode net

On Thu, Jul 05, 2007 at 05:10:57PM -0700, Chris Travers wrote:

I am trying to find a way to select the number format at runtime for
textual representation of numbers. I am currently running 8.1.4 built
from source on Fedora Linux core 5.

I have been trying to use set lc_numeric = various country codes (for
example es_EC), but I am not able to get the format to change from 1.00
to 1,00.

I think you'll need to use to_char():

test=> set lc_numeric to 'es_ES.UTF-8';
SET
test=> select to_char(1.234, '9D999');
to_char
---------
1,234
(1 row)

The file src/backend/utils/adt/pg_locale.c in the PostgreSQL source
code has comments about how various LC_* settings are used in the
backend.

--
Michael Fuhr

On Jul 5, 2007, at 19:47 , Michael Glaesemann wrote:

I don't believe you'll see numbers *as numbers* displayed with the
formatting you desire unless you somehow tell your client (e.g.,
psql) which locale you want to use. I haven't figured out how to do
this yet, though.

I think display depends on whether or not you configured Postgres
with or without --enable-nls. I never have, so I can't really test
this, but I suspect that display would change with the different lc_*
settings as well. For example, setting lc_messages doesn't do
anything on my machine (built without --enable-nls).

Michael Glaesemann
grzm seespotcode net

Michael Fuhr wrote:

I think you'll need to use to_char():

test=> set lc_numeric to 'es_ES.UTF-8';
SET
test=> select to_char(1.234, '9D999');
to_char
---------
1,234
(1 row)

The file src/backend/utils/adt/pg_locale.c in the PostgreSQL source
code has comments about how various LC_* settings are used in the
backend.

Is there a way to accept localized numbers as input?
i.e. '1,39'::numeric?

Michael Glaesemann <grzm@seespotcode.net> writes:

I think display depends on whether or not you configured Postgres
with or without --enable-nls.

No, to_char understands numeric locales regardless of enable-nls.
There is no provision for locale-dependent output from a plain
numeric column; you must use to_char.

regards, tom lane

Chris Travers <chris@travelamericas.com> writes:

Is there a way to accept localized numbers as input?
i.e. '1,39'::numeric?

See to_number().

regards, tom lane

Tom Lane wrote:

Chris Travers <chris@travelamericas.com> writes:

Is there a way to accept localized numbers as input?
i.e. '1,39'::numeric?

See to_number().

Thanks! I somehow missed that function in the docs.

Best Wishes,
Chris Travers

On Jul 5, 2007, at 20:59 , Tom Lane wrote:

Michael Glaesemann <grzm@seespotcode.net> writes:

I think display depends on whether or not you configured Postgres
with or without --enable-nls.

No, to_char understands numeric locales regardless of enable-nls.
There is no provision for locale-dependent output from a plain
numeric column; you must use to_char.

The to_char results I could see from my tests. I was thinking that
perhaps if NLS was enabled, numeric results in, for example, psql
would be displayed even without using to_char. However, I can't seem
to coax the server to give me results in that way. Perhaps I'm just
doing it wrong.

postgres=# \d lc_examples_view
View "public.lc_examples_view"
Column | Type | Modifiers
-------------------+---------+-----------
a_money | money |
a_numeric | numeric |
a_numeric_as_text | text |
View definition:
SELECT lc_examples.a_money, lc_examples.a_numeric, to_char
(lc_examples.a_numeric, '999D99S'::text) AS a_numeric_as_text
FROM lc_examples;

postgres=# select * from lc_examples_view ;
a_money | a_numeric | a_numeric_as_text
---------+-----------+-------------------
$1.23 | 1.23 | 1.23+
(1 row)

postgres=# set lc_numeric to 'es_ES'; set lc_monetary to 'es_ES';
SET
SET
postgres=# select * from lc_examples_view ;
a_money | a_numeric | a_numeric_as_text
---------+-----------+-------------------
Eu1,23 | 1.23 | 1,23+
(1 row)

Just to clarify, I was thinking that perhaps the a_numeric column
would show a result of 1,23 since lc_numeric was set to es_ES. After
testing with --enable-nls, I see that's not the case.

Michael Glaesemann
grzm seespotcode net