Re: indexing just a part of a string

In the last exciting episode, ch(dot)pingel(at)web(dot)de (Christoph Pingel) wrote:
> I'm new to PostgreSQL, and it has been a pleasure so far.
>
> There's just one thing I'm trying to do and I didn't find any hints in
> the manual: I want to index just a part of a string in a column.
>
> The situation: I have roughly 300.000 rows, and in the column we're
> looking at, most of the entries have less than 200 chars. However,
> there are some (very few) that have more than 3000 chars, and
> postmaster relplies that this is too many for the index (b-tree).
>
> So I would like to say 'index only the first 200 chars of the column',
> which will result in a full index of 99.9 % of my entries. I did this
> in MySQL, but I didn't find it in the pg manual.
>
> How do I proceed?

You might look at using a functional index:

<http://www.postgresql.org/docs/7.3/interactive/indexes-functional.html>

You'd therefore create a "first_200_chars()" function, and then create
an index using that function.
--
wm(X,Y):-write(X),write('@'),write(Y). wm('cbbrowne','gmail.com').
http://cbbrowne.com/info/postgresql.html
Signs of a Klingon Programmer #10: "You cannot really appreciate
Dilbert unless you've read it in the original Klingon."