Re: to_date_valid() - Mailing list pgsql-hackers

On 07/04/2016 10:55 PM, Pavel Stehule wrote:
> 2016-07-04 22:15 GMT+02:00 Andreas Karlsson <andreas@proxel.se
> <mailto:andreas@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')
fromdual;> select to_date('20110231', 'YYYYMMDD') from dual>                 *> ERROR at line 1:> ORA-01839: date not
validfor 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