Search Postgresql Archives

Re: Best way to use indexes for partial match at

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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.

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux