Thread: Full-text searching and/or word indexing
Hi, I want to use full text searching on my PostgreSQL 7.0.2 database. Ok, to be exact I want to somehow be able to type the word alien and get a record set containing "Alien", "Alien 3" and "Alien Resurection". ( "Aliens" could should also be returned but that would probably take more time, so I`ll go with plain hits to begin with. ) How can this be done? If it`s not possible using just PostgreSQL, what is the best tool for the job? ( I`m using Python and Zope for the rest of the project, so if anybody has solutions using these tools that would be perfect. ) Thomas
i assume that you're going to know the column name you're searching on in which case, just use the regular expression ~* ie: select title from movies where title ~* 'alien'; -duck On Mon, 26 Jun 2000, Thomas Weholt wrote: > Hi, > > I want to use full text searching on my PostgreSQL 7.0.2 database. Ok, to be > exact I want to somehow be able to type the word > alien and get a record set containing "Alien", "Alien 3" and "Alien > Resurection". ( "Aliens" could should also be returned but that would > probably take more time, so I`ll go with plain hits to begin with. ) > > How can this be done? If it`s not possible using just PostgreSQL, what is > the best tool for the job? ( I`m using Python and Zope for the rest of the > project, so if anybody has solutions using these tools that would be > perfect. ) > > Thomas > ----------------------------------------------------------------------------- david@backpack.com BackPack Software, Inc. www.backpack.com +1 651.645.7550 voice "Life is an Adventure. +1 651.645.9798 fax Don't forget your BackPack!" -----------------------------------------------------------------------------
Thomas Weholt wrote:
>
> Hi,
>
> I want to use full text searching on my PostgreSQL 7.0.2 database. Ok, to be
> exact I want to somehow be able to type the word
> alien and get a record set containing "Alien", "Alien 3" and "Alien
> Resurection". ( "Aliens" could should also be returned but that would
> probably take more time, so I`ll go with plain hits to begin with. )
>
> How can this be done? If it`s not possible using just PostgreSQL, what is
> the best tool for the job? ( I`m using Python and Zope for the rest of the
> project, so if anybody has solutions using these tools that would be
> perfect. )
There is some full-text indexing stuff in contrib. It depends on the
size of your database though, and other things. If you also wanted your
query to match on 'I, Alien' or 'Inalienable Rights' then you need full
text, but just regular expression or LIKE would also work on a smallish
database.
For performance with more than a few thousand records you'll want to use
the full-text index stuff.
Cheers,
Andrew.
--
_____________________________________________________________________
Andrew McMillan, e-mail: Andrew@cat-it.co.nz
Catalyst IT Ltd, PO Box 10-225, Level 22, 105 The Terrace, Wellington
Me: +64 (21) 635 694, Fax: +64 (4) 499 5596, Office: +64 (4) 499 2267
On Mon, 26 Jun 2000, Thomas Weholt wrote:
> I want to use full text searching on my PostgreSQL 7.0.2 database. Ok, to be
> exact I want to somehow be able to type the word
> alien and get a record set containing "Alien", "Alien 3" and "Alien
> Resurection". ( "Aliens" could should also be returned but that would
> probably take more time, so I`ll go with plain hits to begin with. )
Look in the contrib directory under your source tree for Postgres, and
you'll see a library that does full-text indexing.
Brett W. McCoy
http://www.chapelperilous.net
---------------------------------------------------------------------------
Take your work seriously but never take yourself seriously; and do not
take what happens either to yourself or your work seriously.
-- Booth Tarkington