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 <t...@sss.pgh.pa.us> wrote: > Larry White <ljw1...@gmail.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 >
