Re: Best way to use indexes for partial match at

On Fri, Nov 11, 2005 at 11:31:37AM +0200, Andrus wrote:
> Best solution is as follows:
>
> Planner must use index
>
> CREATE TABLE foo ( bar CHAR(10) PRIMARY KEY );
>
> for queries like
>
> SELECT * FROM foo WHERE bar::CHAR(3)='ABC';

Your query is the same as using LIKE, so why not express it that way?
Is it that unreasonable that a PRIMARY KEY should use the most natural
way to order strings for your locale and that if you want to use LIKE
in non-C locales that you need to specify that explicitly?

PRIMARY KEY == UNIQUE + NOT NULL

Incidently, another way might be COLLATE support, something like:

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

But that's already on the cards.

[1] http://gborg.postgresql.org/project/citext/projdisplay.php

--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.