Re: different datatypes in index scan join

Is there a link to some of these discussions?

Jon

On Mon, 23 Jun 2003, Richard Huxton wrote:

> 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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo(at)postgresql(dot)org
>