different datatypes in index scan join
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?
Erik
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 matchIf 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
Is there a link to some of these discussions?
Jon
On Mon, 23 Jun 2003, Richard Huxton wrote:
Show quoted text
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 matchIf 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@postgresql.org
On Monday 23 Jun 2003 10:06 pm, Jonathan Bartlett wrote:
Is there a link to some of these discussions?
Go to http://archives.postgresql.org/ and search for "coercion" or "implicit
coercion" or "implicit cast" or similar on the hackers list.
--
Richard Huxton