Re: partial TEXT search on an index

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.

> 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