Re: ERROR: functions in index expression must be marked IMMUTABLE

Geoff Winkless <pgsqladmin(at)geoff(dot)dj> writes:
> On 26 February 2017 at 16:09, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
> wrote:
>> On 02/26/2017 07:56 AM, Geoff Winkless wrote:
>>> On 26 February 2017 at 10:09, Sven R. Kunze <srkunze(at)mail(dot)de
>>> <mailto:srkunze(at)mail(dot)de>>wrote:
>>>> # create index docs_birthdate_idx ON docs using btree
>>>> (((meta->>'birthdate')::date));
>>>> ERROR: functions in index expression must be marked IMMUTABLE

>>> ​Date functions are inherently not immutable because of timezones.

> ​ Isn't the point that casting to ::timestamp will still keep the
> timezone? Hence casting to "without timezone".

There are multiple reasons why the text-to-datetime conversion functions
are not immutable:

* some of them depend on the current timezone (but I don't believe date_in
does);

* all of them depend on the current datestyle setting, eg to resolve
'02/03/2017';

* all of them accept strings with time-varying values, such as 'now'
or 'today'.

You could get around the second and third points with to_timestamp(),
but since the only variant of that is one that yields timestamptz and
hence is affected by the timezone setting, it's still not immutable.

I'm not entirely sure why the OP feels he needs an index on this
expression. If he's willing to restrict the column to have the
exact format 'YYYY-MM-DD', then a regular textual index would sort
the same anyway. Perhaps what's needed is just to add a CHECK
constraint verifying that the column has that format.

regards, tom lane