Regular Expressions, LIKE, and indexes

From: "Travis Whitton" <tinymountain(at)gmail(dot)com>
To: pgsql-sql(at)postgresql(dot)org
Subject: Regular Expressions, LIKE, and indexes
Date: 2006-11-22 19:10:52
Message-ID: cf9b4f3e0611221110s412dfe4foefa988ef787b30a8@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

From everything I've been able to find, it seems that the only way to get
front-anchored regular expressions or LIKE patterns beginning with constants
to use an index is to have previously initialized your database using the C
locale. Is this still true? I'm trying to do something like:

SELECT keyword FROM keywords WHERE keyword ~ '^foo';

or

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?

Thanks,
Travis

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Richard Huxton 2006-11-22 19:20:52 Re: Regular Expressions, LIKE, and indexes
Previous Message Richard Broersma Jr 2006-11-22 17:26:57 Re: select into