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

Martijn,

>> I can use queries:
>>
>> WHERE col1 BETWEEN 'f' and 'f'+CHR(255)

>Well, you could do that in postgresql too, you just need to use the SQL
>standard concatination operator.

>WHERE col1 BETWEEN 'f' and 'f' || chr(255);

thank you.

I think it is best to use regular indexes since regular indexes since they
can be used in other types of queries also.

It seems that only way is to use BETWEEN comparison for this in Postgres
8.1.

I tried

CREATE TABLE foo ( col1 CHAR(20));
CREATE INDEX i1 ON foo(col1);
INSERT INTO foo VALUES ('bar');
SELECT * FROM foo WHERE col1 BETWEEN 'b' and 'b' || chr(255);

But this does not return any data.

How to write index optimizable WHERE clause when only some (variable number)
of characters from beginning of col1 are known ?

Only way seems to use BETWEEN comparison by concatenating character greater
than all other characters in locale. Since CHR(255) does not work this is
not possible.

So

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.

Andrus.