Re: indexing just a part of a string

You can use a functional index. Something like

CREATE INDEX foo ON bar (substring(blah,1,200))

Should work I think.

Ian Harding
Programmer/Analyst II
Tacoma-Pierce County Health Department
iharding(at)tpchd(dot)org
Phone: (253) 798-3549
Pager: (253) 754-0002

>>> Christoph Pingel <ch(dot)pingel(at)web(dot)de> 02/08/05 2:50 AM >>>
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?

best regards, and TIA
Christoph Pingel

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo(at)postgresql(dot)org