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:

Please share your ideas and opinions about this topic with me, your contribution will add true value to this topic.
If anyone has doubts on this topic then please do let me know by leaving comments or send me an email.

If you like this post, then please share it with others.
Please follow, I will share my experience towards the success of Database Research and Development Activity.

I put up a post every day, please keep reading and learning.
Discover Yourself, Happy Blogging !
Anvesh M. Patel.

More from

Leave a Reply

Be the First to Comment!

Notify of