Re: Best way to use indexes for partial match at beginning

>> CREATE INDEX i1 ON foo(col1);
>>
>> cannot be used to optimize queries of type "get all rows where first n
>> charaters of col1 are known" in Postgres.
>
> Of course it will! Any btree based index will let you do that. Re-read the
> previous answers and make sure you pay attention to the bit about
> text_pattern_ops and LIKE in non-C locales.

Richard,

thank you. I try to formulate my problem more presicely.
I have table

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

Cluster locale is non-C. Database encoding is UTF-8. Postgres vers is 8.1

PRIMARY KEY clause creates btree based index so the index exists on bar.

I want to run fast queries by knowing first characters of bar like :

1. Select records from foo where first character of bar is A
2. Select records from foo where first character of bar is B
3. Select records from foo where first  two characters of bar are BC
4. Select records from foo where first  three characters of bar are ABC
etc.

Can you write sample WHERE clause which can use existing primary key index
for those queries ?

Andrus.