Re: [GENERAL] ERROR: functions in index expression must be markedIMMUTABLE - Mailing list pgsql-general

On 27 February 2017 at 10:52, Sven R. Kunze <srkunze@mail.de> wrote:

So, what can I do to parse texts to date(times) in a safe manner?

You know best the format of your data; if you know that your date field is always in a particular style and timezone, you can write a function that can be considered safe to set IMMUTABLE, where a more generic​ system todate function cannot.

It might be sensible to call the function something that describes it exactly, rather than my_to_date you could call it utc_yyyymmdd_todate or something, just in case someone comes along later and sees an immutable todate function and thinks they can use it for something else.

Geoff

Thanks, Geoff. It's not the most convenient way to define an index to define a function first and that all future queries need to use that very function in order to utilize the index. Though, it's the most safest and best documenting way.


So, I got two additional questions:

Why is this relevant for dates? I cannot see that dates are timezone-influenced.

I still feel that a function is overkill for a simple text to date conversion. Couldn't there be an IMMUTABLE modifier for an expression to mark it as immutable?


"SELECT '2007-02-02'::date;" just works. It would be great if one could define an index with the same ease. I already can see how our application developers need constant reminders that "in case of dates, use 'magic_function' first". If they don't, the application will suffer from bad performance.


Thanks in advance for your replies.

Regards,
Sven