This article is half-done without your Comment! *** Please share your thoughts via Comment ***
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:
|
1 2 3 4 5 |
CREATE TABLE tbl_TestTriGramIndex ( ID INT ,TextData TEXT ); |
Create a Trigram Index on Text Column:
|
1 |
CREATE INDEX trgm_idx_tbl_TestTriGramIndex_TextData ON tbl_TestTriGramIndex USING GIST (TextData gist_trgm_ops); |
If you get below error, install require pg_trgm extension:
|
1 |
ERROR: operator class "gist_trgm_ops" does not exist for access method "gist" |
Install pg_trgm extension:
|
1 |
CREATE EXTENSION pg_trgm; |
Insert few sample records:
|
1 2 3 4 5 6 |
INSERT INTO tbl_TestTriGramIndex VALUES (1,'Anvesh Patel') ,(2,'Database Research & Development') ,(3,'PostgreSQL is better than SQL Server') ,(4,'PostgreSQL is supported powerful BRIN Indexes'); |
Execute few SELECTs for text comparison:
|
1 2 3 4 5 6 7 8 |
SELECT *FROM tbl_TestTriGramIndex WHERE TextData % 'PostgreSQL is'; SELECT *FROM tbl_TestTriGramIndex WHERE TextData LIKE '%SQL%'; SELECT *FROM tbl_TestTriGramIndex WHERE TextData ~* 'ql'; |
Execute below SELECTs and check Trigram generated result of given string:
|
1 2 |
SELECT SHOW_TRGM('Anvesh') AS Anvesh; SELECT SHOW_TRGM('Anvesh Patel') AS AnveshPatel; |
Execute below SELECTs and check percentage of similarity:
|
1 2 |
SELECT SIMILARITY('Anvesh','anv') AS SimilarPercentage; SELECT SIMILARITY('Anvesh','anve') AS SimilarPercentage; |
