{"id":2458,"date":"2020-12-06T05:37:29","date_gmt":"2020-12-06T05:37:29","guid":{"rendered":"https:\/\/system.camp\/?p=2458"},"modified":"2020-12-06T05:39:26","modified_gmt":"2020-12-06T05:39:26","slug":"full-text-search-in-postgresql","status":"publish","type":"post","link":"https:\/\/system.camp\/databases\/full-text-search-in-postgresql\/","title":{"rendered":"Full Text Search in PostgreSQL"},"content":{"rendered":"\n<h3>What is wrong with the good old SQL searches?<\/h3>\n\n\n\n<ul><li>There is no support for common languages. Regular expressions do not suffice because they cannot easily handle words that essentially mean the same, i.e., blocked and blocking is one such example. You might miss documents that contain blocked, although you probably would like to find them when searching for blocking. Now don\u2019t tell me you are going to use OR through all the derived words &#8211; that\u2019s not what a good developer would do \ud83d\ude09<\/li><li>There is no support for indexes and thus, you have to process the whole document every time you need to search something which is rather tedious and slow.<\/li><\/ul>\n\n\n\n<h3>What is full text search?<\/h3>\n\n\n\n<p>As the <a href=\"https:\/\/www.postgresql.org\/docs\/9.5\/textsearch-intro.html\" target=\"_blank\" rel=\"noopener\">official documentation<\/a> defines it &#8211; Full Text Searching (or just text search) provides the capability to identify natural-language documents that satisfy a query, and optionally to sort them by relevance to the query. The most common type of search is to find all documents containing given query terms and return them in order of their similarity to the query. Notions of query and similarity are very flexible and depend on the specific application. The simplest search considers query as a set of words and similarity as the frequency of query words in the document.<\/p>\n\n\n\n<p>This essentially means that you can now just search for block and you\u2019re going get all it\u2019s derivatives in the document that you want! Isn\u2019t that cool?<\/p>\n\n\n\n<h3>How does it work though?<\/h3>\n\n\n\n<p>PostgreSQL has two utility functions that will help us through this quest &#8211; <code>to_tsvector()<\/code> and <code>to_tsquery()<\/code><\/p>\n\n\n\n<ul><li>The <code>to_tsvector()<\/code> command will create a set of lexemes using the document provided to it. It\u2019ll conveniently omit any words that have little meaning &#8211; words like \u2018the\u2019, \u2018an\u2019, etc. When you run this,<br><code>SELECT to_tsvector('english', 'Full text search is an awesome feature');<\/code><\/li><\/ul>\n\n\n\n<p>The result is a \u2018map\u2019 of words or a dictionary which represents the location of each word.<br><code>\u2019awesom\u2019:6\u2019featur\u2019:7\u2019full\u2019:1\u2019search\u2019:3\u2019text\u2019:2<\/code><\/p>\n\n\n\n<ul><li>The <code>to_tsquery()<\/code> takes in a list of words that will be searched against the result of our <code>to_tsvector()<\/code> function.<\/li><\/ul>\n\n\n\n<p><code>SELECT to_tsvector(\u2018Full text search is an awesome feature\u2019) @@ to_tsquery(\u2018full\u2019);<\/code><\/p>\n\n\n\n<p>The query above will give us a result <code>true<\/code>.<\/p>\n\n\n\n<p>Now how to use it? The most apt use would be to create a <code>tsvector<\/code> of the columns of your database you want to search on and run a <code>tsquery<\/code> command against that to select the rows you need. What\u2019s more interesting is that you can use operators like <code>&amp;<\/code>, <code>|<\/code>, <code>!<\/code>, etc in your queries!<\/p>\n\n\n\n<p>The official documentation does an awesome job of explaining things! This is just an overview &#8211; for more details please go through <a href=\"https:\/\/www.postgresql.org\/docs\/9.6\/functions-textsearch.html\" target=\"_blank\" rel=\"noopener\">this<\/a> link.<\/p>\n\n\n\n<p><\/p>\n","protected":false},"excerpt":{"rendered":"<p>What is wrong with the good old SQL searches? There is no support for common languages. Regular expressions do not suffice because they cannot easily handle words that essentially mean the same, i.e., blocked and blocking is one such example. You might miss documents that&#8230;<\/p>\n","protected":false},"author":2,"featured_media":2461,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"om_disable_all_campaigns":false,"_mi_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0},"categories":[70,71,72],"tags":[],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/system.camp\/wp-json\/wp\/v2\/posts\/2458"}],"collection":[{"href":"https:\/\/system.camp\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/system.camp\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/system.camp\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/system.camp\/wp-json\/wp\/v2\/comments?post=2458"}],"version-history":[{"count":1,"href":"https:\/\/system.camp\/wp-json\/wp\/v2\/posts\/2458\/revisions"}],"predecessor-version":[{"id":2460,"href":"https:\/\/system.camp\/wp-json\/wp\/v2\/posts\/2458\/revisions\/2460"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/system.camp\/wp-json\/wp\/v2\/media\/2461"}],"wp:attachment":[{"href":"https:\/\/system.camp\/wp-json\/wp\/v2\/media?parent=2458"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/system.camp\/wp-json\/wp\/v2\/categories?post=2458"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/system.camp\/wp-json\/wp\/v2\/tags?post=2458"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}