Re: Using bigint needs explicit cast to use the index

On Mon, Mar 08, 2004 at 10:26:21AM +1000, Steven Butler wrote:
> I tested my hunch by casting the constant to bigint (as can be seen below)
> and suddenly the query is using the index again.

Yes.  You can make this work all the time by quoting the constant.
That is, instead of

    WHERE indexcolumn = 123

do

    WHERE indexcolumn = '123'


> We are currently using pg 7.3.4.  Is this intended behaviour?  Should the
> constant be cast to the type of the table column where possible, or should

"Intended", no.  "Expected", yes.  This topic has had the best
Postgres minds work on it, and so far nobody's come up with a
solution.  There was a proposal to put in a special-case automatic
fix for int4/int8 in 7.4, but I don't know whether it made it in.

A
--
Andrew Sullivan  | ajs@crankycanuck.ca
I remember when computers were frustrating because they *did* exactly what
you told them to.  That actually seems sort of quaint now.
        --J.D. Baldwin