Обсуждение: Localization trouble
Hi all; 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. Any hints as to what I could be doing wrong? Best Wishes, Chris Travers
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