Re: varchar index joins not working?

Tom Lane wrote:
> Adam Gundy <adam(at)starsilk(dot)net> 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.

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:

'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 ...'

>> 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.