This article is half-done without your Comment! *** Please share your thoughts via Comment ***
In this post, I am sharing few examples of full text searching and pattern matching in PostgreSQL.
PostgreSQL provides the different options for Full text searching and pattern matching.
Here, I am using tsvector for full text search which is document type and uses match operator like @@.
PostgreSQL: Example of Trigram Index for Full Text Search using pg_trgm Extension
Below are the few examples of Full Text Search:
Casting in tsvector:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
SELECT 'I am owner of dbrnd.com'::tsvector @@ 'am & of'::tsquery as Result result --------- true SELECT 'I am owner of dbrnd.com'::tsvector @@ 'am & dbrnd.com'::tsquery as Result result --------- true SELECT 'I am owner of dbrnd.com'::tsvector @@ 'am & dbrnd'::tsquery as Result result --------- false |
Use to_tsvector():
|
1 2 3 4 5 |
SELECT to_tsvector('I am owner of dbrnd.com') @@ to_tsquery('owner & dbrnd.com') as Result result --------- true |
Search adjacent string combination:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
SELECT to_tsvector('anvesh patel') @@ to_tsquery('anvesh <-> patel') as Result result --------- true SELECT to_tsvector('anvesh m patel') @@ to_tsquery('anvesh <-> patel') as Result result --------- false SELECT to_tsvector('anvesh m patel') @@ to_tsquery('anvesh <2> patel') as Result result --------- true |
