On 07/04/2016 10:55 PM, Pavel Stehule wrote:
2016-07-04 22:15 GMT+02:00 Andreas Karlsson <andr...@proxel.se
<mailto:andr...@proxel.se>>:
I do not see a clear conclusion in the linked threads. For example
Bruce calls it a bug in one of the emails
(https://www.postgresql.org/message-id/201107200103.p6K13ix10517%40momjian.us).
I think we should fix to_date() to throw an error. Personally I
would be happy if my code broke due to this kind of change since the
exception would reveal an old bug which has been there a long time
eating my data. I cannot see a case where I would have wanted the
current behavior.
If I remember, this implementation is based on Oracle's behave.
In the thread I linked above they claim that Oracle (at least 10g) does
not work like this.
Thomas Kellerer wrote:
> Oracle throws an error for the above example:
>
> SQL> select to_date('20110231', 'YYYYMMDD') from dual;
> select to_date('20110231', 'YYYYMMDD') from dual
> *
> ERROR at line 1:
> ORA-01839: date not valid for month specified
I do not have access to an Oracle installation so I cannot confirm this
myself.
It is
pretty old and documented - so it is hard to speak about it like the
bug. I understand, so the behave is strange, but it was designed in
different time. You can enter not 100% valid string, but you get correct
date
postgres=# select to_date('2016-12-40','YYYY-MM-DD');
┌────────────┐
│ to_date │
╞════════════╡
│ 2017-01-09 │
└────────────┘
(1 row)
It can be used for some date calculations. In old age the applications
was designed in this style. I am against to change of default behave. We
can introduce new new different function with different name with better
designed format and rules, or we can add new options to this function,
or we can live with current state.
While clever, I think this behavior is a violation of the principle of
least surprise. It is not obvious to someone reading a query that
to_date() would behave like this. Especially when Oracle's to_date()
works differently.
> Now, to_date function should not be used - functions make_date,
> make_timestamp are faster and safe.
Yeah, I personally know of this behavior and therefore would never use
to_date(), but I am far from the average PostgreSQL user.
Andreas
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
