pg.ddx.io pgsql-sql@postgresql.org mailing list archivehelp / color / mirror / Atom feed
From: Richard Huxton <dev@archonet.com>
To: Travis Whitton <tinymountain@gmail.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Regular Expressions, LIKE, and indexes
Date: Wed, 22 Nov 2006 19:20:52 +0000
Message-ID: <4564A314.4090504@archonet.com> (raw)
In-Reply-To: <cf9b4f3e0611221110s412dfe4foefa988ef787b30a8@mail.gmail.com>
References: <cf9b4f3e0611221110s412dfe4foefa988ef787b30a8@mail.gmail.com>
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
view thread (2+ messages)
Message-ID: <4564A314.4090504@archonet.com> Permalink: ../4564A314.4090504@archonet.com/ Also on: postgresql.org/message-id/4564A314.4090504@archonet.com ·
reply
Reply instructions: You may reply publicly to this message via plain-text email using any one of the following methods: * Reply to all the recipients using the --to and --cc options: reply via email To: pgsql-sql@postgresql.org Cc: dev@archonet.com, tinymountain@gmail.com Subject: Re: Regular Expressions, LIKE, and indexes In-Reply-To: <4564A314.4090504@archonet.com> * Save the following mbox file, import it into your mail client, and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions for how to clone and mirror all data and code used for this inbox