On Monday, 25 September 2017, Nathan Wagner <
nw+pg@hydaspes.if.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?