Re: [GENERAL] ERROR: functions in index expression must be markedIMMUTABLE

On 02/26/2017 08:50 AM, Tom Lane wrote:
> Geoff Winkless <pgsqladmin@geoff.dj> writes:
>> On 26 February 2017 at 16:09, Adrian Klaver <adrian.klaver@aklaver.com>
>> wrote:
>>> On 02/26/2017 07:56 AM, Geoff Winkless wrote:
>>>> On 26 February 2017 at 10:09, Sven R. Kunze <srkunze@mail.de
>>>> <mailto:srkunze@mail.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.

The OP is trying to create an index on the value of a jsonb key. Would
the above still apply or am I misunderstanding the reference to column?

or

The below works:

test=> create index docs_birthdate_idx ON docs using btree
((meta->>'birthdate'));
CREATE INDEX

So if the text values of 'birthdate' are consistent the index would work
without the cast?

>
>             regards, tom lane
>


--
Adrian Klaver
adrian.klaver@aklaver.com