PostgreSQL Full Text Search and Trigram Confusion

They serve very different purposes.

  • Full Text Search is used to return documents that match a search query of stemmed words.
  • Trigrams give you a method for comparing two strings and determining how similar they look.

Consider the following examples:

SELECT 'cat' % 'cats'; --true

The above returns true because 'cat' is quite similar to 'cats' (as dictated by the pg_trgm limit).

SELECT 'there is a cat with a dog' % 'cats'; --false

The above returns false because % is looking for similarily between the two entire strings, not looking for the word cats within the string.

SELECT to_tsvector('there is a cat with a dog') @@ to_tsquery('cats'); --true

This returns true becauase tsvector transformed the string into a list of stemmed words and ignored a bunch of common words (stop words – like ‘is’ & ‘a’)… then searched for the stemmed version of cats.

It sounds like you want to use trigrams to auto-correct your ts_query but that is not really possible (not in any efficient way anyway). They do not really know a word is misspelt, just how similar it might be to another word. They could be used to search a table of words to try and find similar words, allowing you to implement a “did you mean…” type feature, but this word require maintaining a separate table containing all the words used in your search field.

If you have some commonly misspelt words/phrases that you want the text-index to match you might want to look at Synonym Dictorionaries

Leave a Comment

Hata!: SQLSTATE[HY000] [1045] Access denied for user 'divattrend_liink'@'localhost' (using password: YES)