Re: to_date_valid()

On 07/04/2016 10:55 PM, Pavel Stehule wrote:
> 2016-07-04 22:15 GMT+02:00 Andreas Karlsson <andreas(at)proxel(dot)se
> <mailto:andreas(at)proxel(dot)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