Re: String searching

On Tue, Nov 18, 2014 at 11:49 AM, Robert DiFalco <robert(dot)difalco(at)gmail(dot)com>
wrote:

> Either I'm not that smart or I am working on too many things at once (or
> both) but making Full Text work seems super tedious. I just have a single
> VARCHAR field for name, so the full name "William S. Burroughs" is a single
> row and column. I want to as simply as possible have the ability to search
> find this record with Will, will, Burr, burroughs, etc.
>
> As far as I can tell, the trigram extension would be the easiest way to
> implement this. It looks like I wouldn't need to mess with vectors, etc. It
> would just look like a standard index and query, right? It seems that if I
> need something more powerful in the future that I could always move to
> ElasticSearch, Sphinx, or something similar.
>
> Does this sound about right?
>

It depends on how complicated you want to make your indexing and searching.
With the FTS in the data store, your updates, deletes, inserts are
automagically handled. You can also trivially combine your full text search
with other columns like "create_date > 2010-01-01" with ease.

The three steps to success are:

1) add a column to store the tsvector with an index on it.
2) Add a trigger to populate this tsvector on insert/update.
3) change your search queries to compute the tsvector of your search term
and compare that to the tsvector column instead of the original column.
4) profit.