Re: indexed range queries on jsonb?

Thank you Tom,

I made the necessary changes and Explain now shows that the query will use
the index.

Thanks again for your help.

On Tue, Aug 26, 2014 at 10:33 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Larry White <ljw1001(at)gmail(dot)com> writes:
> > Logically, what I want is to be able to make queries like this:
> > select * from document where ((payload->'intTest')) > 5;
> > With casting, I came up with:
> > select * from document where (((payload->'intTest'))::text)::integer > 5;
> > But this query does not use the index according to Explain
>
> Nope. You would have to create an index on the casted expression if you
> want to use integer comparisons with the index. The raw -> expression is
> of type jsonb, which doesn't sort the same as integer.
>
> BTW, you could save a small amount of notation with the ->> operator, ie
> (payload->>'intTest')::integer
>
> regards, tom lane
>