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

On 02/26/2017 09:42 AM, Tom Lane wrote:
> Adrian Klaver <adrian.klaver@aklaver.com> writes:
>> On 02/26/2017 08:50 AM, Tom Lane wrote:
>>> 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?
>
> Sure, I was using "column" loosely to refer to the meta->>'birthdate'
> expression.

Alright, thanks. It is just that with array/hstore/json(b) I see a table
in a column in a table and I need to be clear in my mind what is being
referred to.

>
>> 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?
>
> Yeah, seems to me you could do things like
>     ... WHERE meta->>'birthdate' > '2017-02-26'
> and it would Just Work, though I'd admit there's a deficiency of sanity
> checking for the RHS constant in this example.
>
>             regards, tom lane
>


--
Adrian Klaver
adrian.klaver@aklaver.com