Обсуждение: Regular Expressions, LIKE, and indexes

From everything I've been able to find, it seems that the only way to get front-anchored regular expressions or LIKE
patternsbeginning with constants to use an index is to have previously initialized your database using the C locale. Is
thisstill true? I'm trying to do something like: <br /><br />SELECT keyword FROM keywords WHERE keyword ~ '^foo';<br
/><br/>or<br /><br />SELECT keyword FROM keywords WHERE keyword like 'foo%';<br /><br />Are there any other functions
thatcan provide equivalent results while using an index, or am going to have drop UTF-8 and recreate my database? <br
/><br/>Thanks,<br />Travis<br /> 

Travis Whitton wrote:
> 
> SELECT keyword FROM keywords WHERE keyword like 'foo%';
> 
> Are there any other functions that can provide equivalent results while
> using an index, or am going to have drop UTF-8 and recreate my database?

UTF-8 isn't your locale, it's your character set (or encoding). Your 
locale is something like en_GB.UTF-8 (for British sorting).

You need to define your indexes in such a way that the LIKE can figure 
things out. That means using a special operator-class (so that you 
redefine how less-than and greater-than are defined). It takes longer to 
explain than to do, so I'll direct you to the relevant "CREATE INDEX" 
example in the manuals:  http://www.postgresql.org/docs/8.1/static/indexes-opclass.html

--   Richard Huxton  Archonet Ltd