Re: Best way to use indexes for partial match at

On Fri, 11 Nov 2005, Andrus wrote:

> >> SELECT * FROM foo WHERE bar::CHAR(3)='ABC';
>
> > Your query is the same as using LIKE, so why not express it that way?
>
> I want simply to select by first 3 characters. LIKE is too powerful and
> unnessecary. LIKE requires
> escaping % and ? characters in pattern.
>
> I expected that making planner to use primary key index in case of
>
> WHERE bar::CHAR(3)='ABC'
> WHERE bar BETWEEN 'ABC' AND 'ABC' || CHR(255)
> WHERE SUBSTRING( bar FOR 3 )='ABC'

Which doesn't necessarily work because of how some collations may work.
The first 3 characters of the string may sort to a fundamentally different
place than the first 4 characters. You can have a collation where
'ABC' < 'ABCR' < 'ABCT' < 'ABD' < 'ABZ' < 'ABCS'.  In the above, the ABCS
value shortens so it should be found, however it's sort position in the
index is outside the normal ABC range. AFAIK the interfaces being used
don't give enough information to detect such locales to handle them
differently.