Re: partial TEXT search on an index

On Tue, 31 Mar 2009, Thomas Pundt wrote:

> Hi,
>
> Ow Mun Heng schrieb:
>> I don't think I understand how PG implements fulltext searching or if my
>> search actually needs to use fulltext search.
>
> No, that's something different. See the fine manual.

in 8.4 it will be possible to use prefix search in full-text search,
eg, to_tsquery('AAA:*')

>
>> basically, I have a btree index on a SERIAL_NUMBER which is of sort like
>> ABC12345 or AAA123434 or AAB131441
>>
>> I would like to have search on the specific text of the SERIAL_NUMBER
>> eg:
>> select * from table where serial_number LIKE 'AAA%'
>
> Depending on the PostgreSQL version and locale you are using chances
> are, that you need to create another index on that column to make the
> query use an index. C.f.
>
> http://www.postgresql.org/docs/8.3/interactive/indexes-opclass.html
>
> CREATE INDEX another_index ON your_table (serial_number varchar_pattern_ops);
>
>> does not use the index. What Do i need to do to have it recognise the
>> "partial index?" (is it called?)
>
> No, that's not a partial index (look at e.g. wiki for an explanation).
>
> Ciao,
> Thomas
>
>

Regards,
Oleg
_____________________________________________________________
Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru),
Sternberg Astronomical Institute, Moscow University, Russia
Internet: oleg(at)sai(dot)msu(dot)su, http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83