Re: Best way to use indexes for partial match at

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

or in

WHERE bar BETWEEN 'ABC' AND 'ABC' || CHR(255)

or in

WHERE SUBSTRING( bar FOR 3 )='ABC'

is more simpler than dealing with locale problems in WHERE bar LIKE 'ABC%':

Using LIKE with index optimization in Postgres in non-C locale requires

a. creating non-SQL standard compatible index unsin operator class
b. requires that primary key column has two indexes

This is too much overhead.

>Is it that unreasonable that a PRIMARY KEY should use the most natural
> way to order strings for your locale

This is very reasonable. PRIMARY KEY must use locale order always.

> and that if you want to use LIKE
> in non-C locales that you need to specify that explicitly?

This is unreasonable.

If I use SQL standard way to create table

CREATE TABLE foo ( bar CHAR(10) PRIMARY KEY );

and use SQL standard WHERE clause

WHERE bar LIKE 'ABC%'

or

WHERE bar::CHAR(3)='ABC'

I expect that primary key index can be used without non-standard extensions
to SQL language

Andrus.