Thread

1. Full-text searching and/or word indexing

   Thomas Weholt <thomas@cintra.no> — 2000-06-26T07:06:16Z

   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
   
   

2. Re: Full-text searching and/or word indexing

   D. Duccini <duccini@backpack.com> — 2000-06-26T08:06:29Z

   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!"   
   -----------------------------------------------------------------------------
   
   
   
   

3. Re: Full-text searching and/or word indexing

   Brett W. McCoy <bmccoy@chapelperilous.net> — 2000-06-26T10:46:44Z

   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
   
   
   

4. Re: Full-text searching and/or word indexing

   Andrew McMillan <andrew@catalyst.net.nz> — 2000-06-26T11:46:42Z

   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