Re: decimal seperator - Mailing list pgsql-novice

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