Full text search in postgres

Hi,

Here is the few things while working with full text search in postgres sql.

The first preference is by indexing the columns. The second one is by using lexema search

To implement full text search we have two functions in postgres tsvector from a document and a tsquery from a user query

PostgreSQL provides the function to_tsvector for converting a document to the tsvector data type.

SELECT to_tsvector('english', 'a man  bat rat on a doll - it took the ball');

> "'ball':11 'bat':3 'doll':7 'man':2 'rat':4 'took':9"


In the above result the tsvector does not contain a,on ..

 tsvector internally do parsing by breaking up to tokens and using some logic internally. To know more about what's tsvector actually doing please go through the postgres site

The next function tsquery:

PostgreSQL provides the functions to_tsquery for converting a query to the tsquery data type.

to_tsquery creates a tsquery value from querytext, which must consist of single tokens separated by the Boolean operators & (AND), | (OR) and ! (NOT).

 SELECT to_tsquery('english', 'The & mat & bats');
> "'mat' & 'bat'"


By using the above tow functions we can implement text search in postgres. Below is the sql query to implement it

select * from users where to_tsvector('english', description) @@ to_tsquery('english', 'good | person');


the above query will do the full text search in the description column in the user table.

Thank You,
Uma Mahesh.