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

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

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



---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

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

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

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



---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

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?

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

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

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org/

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

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org/

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

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org/

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




---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings