Re: Full Text Index Scanning

Thanks Oleg. I'm going to have to experiment with this so that I understand
it better.

Matt

On Fri, Jan 28, 2011 at 1:12 PM, Oleg Bartunov <oleg(at)sai(dot)msu(dot)su> wrote:

> Matt, I'd try to use prefix search on original string concatenated with
> reverse string:
>
> Just tried on some spare table
>
> knn=# \d spot_toulouse
> Table "public.spot_toulouse"
> Column | Type | Modifiers
> ---------------------+-------------------+-----------
> clean_name | character varying |
>
>
> 1. create index knn=# create index clean_name_tlz_idx on spot_toulouse
> using gin(to_tsvector('french', clean_name || ' ' || reverse(clean_name)));
> 2.
> select clean_name from spot_toulouse where to_tsvector('french',
> clean_name|| ' ' || reverse(clean_name) ) @@ to_tsquery('french','the:* |
> et:*');
>
> Select looks cumbersome, but you can always write wrapper functions. The
> only drawback I see for now is that ranking function will a bit confused,
> since coordinates of original and reversed words will be not the same, but
> again, it's possible to obtain tsvector by custom function, which aware
> about reversing.
>
> Good luck and let me know if this help you.
>
> Oleg
>
>
> On Fri, 28 Jan 2011, Matt Warner wrote:
>
> I'm in the process of migrating a project from Oracle to Postgres and have
>> run into a feature question. I know that Postgres has a full-text search
>> feature, but it does not allow scanning the index (as opposed to the
>> data).
>> Specifically, in Oracle you can do "select * from table where
>> contains(colname,'%part_of_word%')>1". While this isn't terribly
>> efficient,
>> it's much faster than full-scanning the raw data and is relatively quick.
>>
>> It doesn't seem that Postgres works this way. Attempting to do this
>> returns
>> no rows: "select * from table where to_tsvector(colname) @@
>> to_tsquery('%part_of_word%')"
>>
>> The reason I want to do this is that the partial word search does not
>> involve dictionary words (it's scanning names).
>>
>> Is this something Postgres can do? Or is there a different way to do scan
>> the index?
>>
>> TIA,
>>
>> Matt
>>
>>
> 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
>