Skip site navigation (1) Skip section navigation (2)

Re: numeric_to_number() function skipping some digits

From: Jeevan Chalke <jeevan(dot)chalke(at)enterprisedb(dot)com>
To: Brendan Jurd <direvus(at)gmail(dot)com>
Cc: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, pgsql-hackers(at)postgresql(dot)org
Subject: Re: numeric_to_number() function skipping some digits
Date: 2009-09-22 04:57:19
Message-ID: be46a4f30909212157o71dc82bep7e074f9fa7eb1d14@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-hackers
Hi,

On Mon, Sep 21, 2009 at 12:36 PM, Brendan Jurd <direvus(at)gmail(dot)com> wrote:

> 2009/9/21 Jeevan Chalke <jeevan(dot)chalke(at)enterprisedb(dot)com>:
> > Oracle returns "19-SEP-09" irrespective of the format.
> > Here in PG, we have getting the proper date irrespective of the format as
> > Oracle. But in the case to to_number the returned value is wrong. For
> > example following query returns '340' on PG where as it returns '3450' on
> > Oracle.
> >
> > select to_number('34,50','999,99') from dual;
> >
>
> Hi Jeevan,
>
> Thanks for checking up on the Oracle behaviour.  It appears to
> silently disregard grouping characters in the format pattern, and also
> disregard them wherever they appear in the input string (or else it
> reads the string from right-to-left?).
>

It seems that Oracle reads formatting string from right-to-left. Here are
few results:
('number','format') ==> Oracle          PG
--------------------------------------------
('34,50','999,99')  ==> 3450            340
('34,50','99,99')   ==> 3450            3450
('34,50','99,999')  ==> Invalid Number  3450
('34,50','999,999') ==> Invalid Number  340


>
> It seems that, to match Oracle, we'd need to teach the code that 'G'
> and ',' are no-ops for to_number(), and also that such characters
> should be ignored in the input.
>

That means we cannot simply ignore such characters from the input. Rather we
can process the string R-L. But yes this will definitely going to break the
current applications running today.


> To be honest, though, I'm not sure it's worth pursuing.  If you want
> to feed in numbers that have decorative characters all through them,
> it's far more predictable to just regex out the cruft and use ordinary
> numeric parsing than to use to_number(), which is infamous for its
> idiosyncrasies:
>
> # SELECT regexp_replace('34,50', E'[\\d.]', '', 'g')::numeric;
> 3450
>

This (with E'[^\\d.]') ignores/replaces all the characters except digits
from the input which we certainly not wishing to do. Instead we can continue
with the current implementation. But IMHO, somewhere in the time-line we
need to fix this.


> Cheers,
> BJ
>


Thanks
-- 
Jeevan B Chalke
EnterpriseDB Software India Private Limited, Pune
Visit us at: www.enterprisedb.com
---
If better is possible, then good is not enough

In response to

Responses

pgsql-hackers by date

Next:From: Heikki LinnakangasDate: 2009-09-22 07:11:40
Subject: Re: Hot Standby 0.2.1
Previous:From: Tom LaneDate: 2009-09-22 04:07:40
Subject: Re: TODO item: Allow more complex user/database default GUC settings

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group