Thread: decimal seperator

On 1/4/2011 8:16 PM, Jasmin Dizdarevic wrote:
> Hi,
>
> is there a way to change the way how postgresql outputs floating point
> numbers?
>
> For example:
> Instead of 2.34 => 2,34 or
> Instead of 334,323.53 => 334.323,53
>
> Can this be adjusted per session?
>
> Regards,
> Jasmin
>
>
I am afraid that it can't be adjusted. You can use to_char and to_number
to get the desired format, but PostgreSQL only accepts the standard SQL
notation. I tried playing with setting LC_NUMERIC, but no luck. It
displays messages in the different format but nothing helps with the
numbers.

--
Mladen Gogala
Sr. Oracle DBA
1500 Broadway
New York, NY 10036
(212) 329-5251
www.vmsinfo.com

On 5 January 2011 14:16, Jasmin Dizdarevic <jasmin.dizdarevic@gmail.com> wrote:
> Hi,
> is there a way to change the way how postgresql outputs floating point
> numbers?
> For example:
> Instead of 2.34 => 2,34 or
> Instead of 334,323.53 => 334.323,53
> Can this be adjusted per session?

Does this help?
http://www.postgresql.org/docs/current/interactive/runtime-config-client.html#GUC-LC-NUMERIC



> Regards,
> Jasmin

Cheers,
Andrej




--
Please don't top post, and don't use HTML e-Mail :}  Make your quotes concise.

http://www.georgedillon.com/web/html_email_is_evil.shtml

On 1/4/2011 8:39 PM, Andrej wrote:
> On 5 January 2011 14:16, Jasmin Dizdarevic<jasmin.dizdarevic@gmail.com>  wrote:
>> Hi,
>> is there a way to change the way how postgresql outputs floating point
>> numbers?
>> For example:
>> Instead of 2.34 =>  2,34 or
>> Instead of 334,323.53 =>  334.323,53
>> Can this be adjusted per session?
> Does this help?
> http://www.postgresql.org/docs/current/interactive/runtime-config-client.html#GUC-LC-NUMERIC
>


Not really:

mgogala=# set lc_numeric='de_DE';
SET
mgogala=# select 1/5::float;
  ?column?
----------
       0.2
(1 row)

mgogala=#


As you can see, I did set lc_numeric to the locale which uses decimal
comma, not the decimal point and got back a point. I would have to do
that with to_char function.

--
Mladen Gogala
Sr. Oracle DBA
1500 Broadway
New York, NY 10036
(212) 329-5251
www.vmsinfo.com

On 1/4/2011 8:46 PM, Jasmin Dizdarevic wrote:
> That's the problem. I've also played around with it, without luck.
>
> How other rdbms does handle this?

Which one? There are many of them RDBMSes. Here is an example from the
database made by the same company that also makes MySQL:


SQL> alter session set NLS_NUMERIC_CHARACTERS=',.';

Session altered.

SQL> select 1/5 from dual;

        1/5
----------
         ,2

SQL>

--
Mladen Gogala
Sr. Oracle DBA
1500 Broadway
New York, NY 10036
(212) 329-5251
www.vmsinfo.com

Hello all,

implementing lc_numeric is on "todo" since years.

There is not only the difference between dot and comma.
Some languages uses single quotes instead of dot or comma.

It is not easy to implement lc_numeric.
One simple example what already could get a problem with comma using:
INSERT into t VALUES (3,5);

What shall be inserted? Two integers 3 and 5 or 3,5 as decimal/numeric?

As you see, already this simple query is a problem.
It will get more ugly when you thing about more complex stuff.

> How other rdbms does handle this?

My information is that only Oracle supports it.
And they have lots of trouble with it.
In Oracle the example above will insert two integers.
You need to quote the decimal to get it as decimal.
And also Oracle will behave ugly in deeper areas ....
I made bad experiences here with German Oracle, decimals and
regular expressions.

Thinking about programming languages then only Java
supports comma instead of dot. JDBC is translating comma
into dot and dot into comma by automatism and transparent
when language settings are correct.

For input data you could play with to_number():

select to_number('1.000,56', '9G999D99'); => 1000.56

select to_number('-1.234,67','S9G999D99'); => -1234.67

For output data you could play with to_char():

select to_char(1000.56, '9G999D99'); => 1000,56

select to_char(-1234.67, 'S9G999D99'); => -1.234,67

But you have to be careful here.
select to_number('-1.234,67','999G999D99'); => -1.24

Which means you have to know how much digits you have.

More about to_number and to_char you will find here:
http://www.postgresql.org/docs/9.0/static/functions-formatting.html

I know it will cost a little bit performance but what I usually do here is:
First of all I look into the manual and check if the driver/interface
(odbc, jdbc, dbd, ...) supports conversion.
As I said before, my experience is that only JDBC is supporting it.

If driver/interface is not supporting it then ...
I have to check input anyway for several reasons like security and if the
user really filled a number and not some chars and so on.
During this input-check I just check manually if dot or comma is used and
convert it into SQL design.

Btw. my bank force to use comma (I have no clue which RDBMS they use).
When I want to transfer 3 Euro and 50 Cent and I fill 3.5 then it converts
it into 3500 Euro ... better not clicking ok and just change it to 3,5.


Susanne

--
Susanne Ebrecht - 2ndQuadrant Deutschland
PostgreSQL Development, 24x7 Support, Training and Services
www.2ndQuadrant.com

Jasmin Dizdarevic <jasmin.dizdarevic@gmail.com> wrote:

> Hi,
>
> is there a way to change the way how postgresql outputs floating point numbers?

Yeah, you can and should use to_char(), example:

test=# select to_char(2.34,'999D99');
 to_char
---------
    2.34
(1 Zeile)

Zeit: 0,153 ms
test=*# set lc_numeric = 'de_DE.UTF-8';
SET
Zeit: 0,122 ms
test=*# select to_char(2.34,'999D99');
 to_char
---------
    2,34
(1 Zeile)


It works also for the group separator:

test=*# select to_char(1234567.89,'999G999G999D99');
     to_char
-----------------
    1.234.567,89


You have to use to_char(), but i think, this is exactly what you are
looking for.

>
> Can this be adjusted per session?

Sure ;-)


Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect.                              (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly."   (unknown)
Kaufbach, Saxony, Germany, Europe.              N 51.05082°, E 13.56889°

Susanne Ebrecht wrote:
> My information is that only Oracle supports it.
>
I think that DB2 does that, too.

> And they have lots of trouble with it.
>
True. That is an infinite source of fun and games for me. I read your
presentation from PGCon 2008 about the lessons that can be learned from
MySQL. Great points, good job.

--

Mladen Gogala
Sr. Oracle DBA
1500 Broadway
New York, NY 10036
(212) 329-5251
http://www.vmsinfo.com
The Leader in Integrated Media Intelligence Solutions

Susanne Ebrecht wrote:
> Hello all,
> There is not only the difference between dot and comma.
> Some languages uses single quotes instead of dot or comma.
>
> It is not easy to implement lc_numeric.
>
> implementing lc_numeric is on "todo" since years.

Sorry for the late reply.  FYI, I do not remember this being a TODO and
do not see it on our TODO list:

    http://wiki.postgresql.org/wiki/Todo

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

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

Bruce Momjian <bruce@momjian.us> writes:
> Susanne Ebrecht wrote:
>> implementing lc_numeric is on "todo" since years.

> Sorry for the late reply.  FYI, I do not remember this being a TODO and
> do not see it on our TODO list:

>     http://wiki.postgresql.org/wiki/Todo

I'm fairly sure that we've discussed allowing comma instead of dot in
regular numeric value I/O (ie, not in to_char() or other specialized
textual conversions) and explicitly rejected it as being more likely to
break things than anything else.  An example of the sort of thing likely
to go wrong is that application code might not think that a numeric
value could ever need quoting to go into CSV data.

            regards, tom lane