Re: Fix number skipping in to_number

On Monday, 25 September 2017, Nathan Wagner <nw+pg(at)hydaspes(dot)if(dot)org> wrote:

> On Thu, Aug 17, 2017 at 12:33:02PM +0100, Oliver Ford wrote:
>
> > Ok I've made that change in the attached v3. I'm not sure as I'm on
> > en_US.UTF-8 locale too. Maybe something Windows specific?
>
> This patch applies against master (8485a25a), compiles, and
> passes a make check.
>
> I tested both on my mac laptop, and my linux server.
>
> If we want this patch, I'd say it's ready for committer. We may want
> (and I can't believe I'm saying this) more discussion as to exactly what
> the strategy for to_number() (and friends) is. Do we want to duplicate
> Oracle's functionality, or do we want a similar function to do similar
> things, without necessarily having a goal of identical behavior to
> oracle?
>
> For myself, I pretty much never use the to_date, to_number, or
> to_timestamp functions except when porting oracle code. I do use the
> to_char functions on occasion. If strftime were available, I probably
> wouldn't use them.
>
> I would commit this patch and update the TODO with a goal of making
> to_number as Oracle compatible as is reasonable.
>
>
Thanks for your review. The issue is that Oracle throws errors on many more
input cases than Postgres does, so making it exactly like Oracle could
break a lot of existing users. E.g. to_number ('123,000', '999') returns
'123' on Postgres, but throws an error on Oracle. So making it exactly
Oracle-like could break existing users who might rely on the current
behavior.

My view is that we shouldn't deliberately introduce errors in order to be
exactly like Oracle if we don't currently and there's a sane use case for
current behavior. Do you have any examples of results that are different
between Oracle and Postgres, and you think the Oracle result makes more
sense?