Re: [PERFORM] varchar index joins not working?

[Adam Gundy]

Tom Lane wrote:

Adam Gundy <[EMAIL PROTECTED]> writes:

Tom Lane wrote:

Oh, it coerces the type all right, just not in the direction you'd like.

is there a reason it doesn't coerce to a type that's useful to the planner (ie varchar in my case),

In this case I think the choice is probably semantically correct:
shouldn't a comparison of varchar (trailing space sensitive) and
char (trailing space INsensitive) follow trailing-space-insensitive
semantics?

I wouldn't swear that the behavior is intentional ;-) as to going
that way rather than the other, but I'm disinclined to change it.

  'I could make your query go much faster IF ...'

or

  'suggestion: create an index on ...'
  'suggestion: convert this index to ...'

or

   'warning: I'd really like to use this index, BUT ...'

ahh. I forgot about the trailing spaces. but you can always coerce a char to a varchar safely, which would have fixed my issue. you can't coerce the other way, as you say, because you'll lose the trailing spaces... alternatively, can the planner give warnings somehow? or suggestions? eg some messages from 'explain analyze' like:

or the planner doesn't accept any type of string as a valid match for index scan?

Can't.  This equality operator doesn't have the same notion of equality
that that index does.

The long and the short of it is that mixing char and varchar is
hazardous.

no kidding.

smime.p7s
Description: S/MIME Cryptographic Signature