Re: to_date_valid()

2016-07-04 22:15 GMT+02:00 Andreas Karlsson <andreas(at)proxel(dot)se>:

> On 07/03/2016 12:36 PM, Andreas 'ads' Scherbaum wrote:
>
>> On 03.07.2016 07:05, Jaime Casanova wrote:
>>
>>> Shouldn't we fix this instead? Sounds like a bug to me. We don't usually
>>> want to be bug compatible so it doesn't matter if we break something.
>>>
>>
>> There are previous discussions about such a change, and this was rejected:
>>
>> https://www.postgresql.org/message-id/lbjf1v%24a2v%241%40ger.gmane.org
>>
>> https://www.postgresql.org/message-id/A737B7A37273E048B164557ADEF4A58B17C9140E%40ntex2010i.host.magwien.gv.at
>>
>>
>> Hence the new function, which does not collide with the existing
>> implementation.
>>
>
> 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. 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.

Now, to_date function should not be used - functions make_date,
make_timestamp are faster and safe.

postgres=# select make_date(2017,01,40);
ERROR: date field value out of range: 2017-01-40

Regards

Pavel

> If there is any legitimate use for the current behavior then we can add it
> back as another function.
>
> Andreas
>
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>