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 <o...@sai.msu.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: o...@sai.msu.su, http://www.sai.msu.su/~megera/ > phone: +007(495)939-16-83, +007(495)939-23-83 >