PostgreSQL: Example of Trigram Index for Full Text Search using pg_trgm Extension

In the PostgreSQL, we can use Trigram to enhance the use of Full Text Search.
The Trigram algorithm divides string like “dog” = “d”,”do”,”dog”,”og”.

The Trigram matching comes with pg_trgm extension.
We can also apply Trigram operators on Gist and GIN indexes which increase the search speed and enhance the performance.

We have already different thesaurus or synomyms for the Full Text Search, but we can use Trigram for implementing typing corrections and suggestions.

Here, you can find different examples of this.

Create a sample table:

Create a Trigram Index on Text Column:

If you get below error, install require pg_trgm extension:

Install pg_trgm extension:

Insert few sample records:

Execute few SELECTs for text comparison:

Execute below SELECTs and check Trigram generated result of given string:

Execute below SELECTs and check percentage of similarity:

Anvesh Patel

Leave a Reply

Be the First to Comment!

Notify of
avatar
wpDiscuz