Re: bpchar, text and indexes

Victor Yegorov <vyegorov(at)gmail(dot)com> writes:
> Well, for `varchar` type Postgres is able to do `varchar` -> `bpchar` cast
> for my constant. I do not understand why for `text` it cannot and casts
> column instead.

In cross-type comparisons like these, the parser basically has a choice
between whether to apply texteq or bpchareq. It's going to have to cast
one side or the other either way. It prefers to cast to text, not away
from text, because text is the preferred type in the string category.
So "bpchar = text" is resolved as texteq.

In the "bpchar = varchar" case, that doesn't happen because neither side
is text to start with, so bpchareq is chosen on the grounds of requiring
fewer casts.

There's no varchareq operator (because varchar just relies on text's
operators). If there were, you'd probably get an "ambiguous operator,
please cast" error from "bpchar = varchar", because neither of those are
preferred types so there would be no way to prefer one interpretation
over the other. Similarly, if text weren't treated as a preferred type
then "bpchar = text" would just fail, it would still not be resolved
the way you want.

See
http://www.postgresql.org/docs/9.5/static/typeconv-oper.html
for a more detailed explanation.

regards, tom lane