Re: select fails on indexed varchars.

Alex Krohn <alex@gossamer-threads.com> writes:
>> What does EXPLAIN VERBOSE select * from foo where a like 'Test/%';
>> show?

Well, the indexqual is just what it should be for C locale:

:indxqual ((
{ EXPR :typeOid 16  :opType op :oper
{ OPER :opno 1061 :opid 1052 :opresulttype 16 } :args (
{ VAR :varno 1 :varattno 1 :vartype 1042 :vartypmod 29  :varlevelsup 0 :varnoold 1 :varoattno 1}
{ CONST :consttype 1042 :constlen -1 :constisnull false :constvalue  9 [ 9 0 0 0 84 101 115 116 47 ]  :constbyval false
})} 
{ EXPR :typeOid 16  :opType op :oper
{ OPER :opno 1058 :opid 1049 :opresulttype 16 } :args (
{ VAR :varno 1 :varattno 1 :vartype 1042 :vartypmod 29  :varlevelsup 0 :varnoold 1 :varoattno 1}
{ CONST :consttype 1042 :constlen -1 :constisnull false :constvalue  9 [
9 0 0 0 84 101 115 116 48 ]  :constbyval false })}))

This mess translates as

    a >= 'Test/'::bpchar AND a < 'Test0'::bpchar

which is what the LIKE index optimizer is supposed to generate.
I infer that one or the other of these conditions yields false on your
machine, which should not be happening if the thing is in C locale.

            regards, tom lane