Re: to_date_valid()

On 04.07.2016 16:33, Amit Kapila wrote:
> On Sun, Jul 3, 2016 at 6:34 AM, Andreas 'ads' Scherbaum
> <adsmail(at)wars-nicht(dot)de> wrote:
>>
>> Hello,
>>
>> we have customers complaining that to_date() accepts invalid dates, and
>> returns a different date instead. This is a known issue:
>>
>> http://sql-info.de/postgresql/notes/to_date-to_timestamp-gotchas.html
>>
>> On the other hand this leads to wrong dates when loading dates into the
>> database, because the database happily accepts invalid dates and ends up
>> writing something completely different into the table.
>>
>> The attached patch adds a new function "to_date_valid()" which will validate
>> the date and return an error if the input and output date do not match.
>> Tests included, documentation update as well.
>>
>
> It seems that you are calling many additional function calls
> (date_out, timestamp_in, etc.) to validate the date. Won't the
> additional function calls make to_date much costlier than its current
> implementation? I don't know if there is a better way, but I think it
> is worth to consider, if we can find a cheaper way to detect validity
> of date.

It certainly is costlier, and I'm open to suggestions how to improve the
performance. That is one of the reasons why I considered a separate
function, instead of adding this to to_date() and add even more overhead
there.

> Note - Your patch is small (~13KB) enough that it doesn't need to zipped.

It's not about the small size, it's about websites like Gmail which
mingle up the linebreaks and then git fails. Ran into this problem on
the pgAdminIII list a while ago, ever since I just zip it and avoid the
trouble.

--
Andreas 'ads' Scherbaum
German PostgreSQL User Group
European PostgreSQL User Group - Board of Directors
Volunteer Regional Contact, Germany - PostgreSQL Project