Thread: Text Searching?

I have a couple of best practice questions for doing web searches on
Postgres tables.  Let's say I have a table of employees with A LOT of rows
that I would like to regularly do searches on (from a PHP web page, etc):


EMPLOYEES (table)
--------------------------------
empl_id        pri key
firstname        text or varchar
middlename        text or varchar
lastname        text or varchar
fullname        text or varchar  (firstname+middle+lastname)

Contrived, I know. ANYWAY...

A. Any suggestions for doing full-text searches in Postgres? I have some
primitive ideas of how to do this, but from what I can see, using LIKE
together with an expression that starts with "%" does not make use of the
indexes I have created. How can I set myself up to perform the fastest
possible search?

B. How should the fields be defined? Should they be VARCHAR with a character
limit or just TEXT,etc?

Thanks,

Aaron

On Sun, Oct 09, 2005 at 10:23:13PM -0500, Announce wrote:
> A. Any suggestions for doing full-text searches in Postgres?

See the contrib/tsearch2 and contrib/pg_trgm modules.

> B. How should the fields be defined? Should they be VARCHAR with a character
> limit or just TEXT,etc?

The underlying implementation is the same so ultimately it probably
doesn't matter.  Some people like to use TEXT with a CHECK constraint
on the length because it's easier to change, especially in versions
of PostgreSQL prior to 8.0.

--
Michael Fuhr