Re: different datatypes in index scan join

On Monday 23 Jun 2003 7:08 pm, Erik Price wrote:
> At the bottom of an email from this list, I saw:
> > -------------------------(end of broadcast)-------------------------
> > TIP 9: the planner will ignore your desire to choose an index scan
> > if your joining column's datatypes do not match
>
> If that's so, then what is the recommended way to ensure an index is
> used if we want to join on different datatypes?

Cast your datatypes. For example, if you have an index on an int8 field foo
you'd use something like:

WHERE foo = 7::int8;

This is because the '7' is going to be assumed an int4. You could use the
more-standard cast() function rather than the '::' operator if you don't mind
the typing.

This is part of PG's tradeoff where a flexible type system means being quite
cautious about assumptions. Readers who know an easy way of having their cake
and eating it should contact the hackers list as soon as they've read the
archive discussions on type issues.

--
Richard Huxton