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@xxxxxxxxxxxxx> wrote:
Larry White <ljw1001@xxxxxxxxx> writes:Nope. You would have to create an index on the casted _expression_ if you
> 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
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
