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