{"id":207,"date":"2021-04-20T14:47:17","date_gmt":"2021-04-20T14:47:17","guid":{"rendered":"https:\/\/okankaradag.com\/?p=207"},"modified":"2021-04-20T14:54:56","modified_gmt":"2021-04-20T14:54:56","slug":"postgresql-full-text-search-nasil-kullanilir","status":"publish","type":"post","link":"https:\/\/okankaradag.com\/en\/postgresql\/postgresql-full-text-search-nasil-kullanilir","title":{"rendered":"How to use Postgresql Full Text Search"},"content":{"rendered":"<p>Merhaba, bu yaz\u0131mda postgresql full text search&#8217;\u00fcn nas\u0131l kullan\u0131ld\u0131\u011f\u0131ndan bahsedece\u011fim ve ger\u00e7ek bir tabloda nas\u0131l h\u0131zl\u0131 arama yapabilece\u011fimizi inceleyece\u011fiz.<\/p>\n\n\n\n<p>\u00d6ncelikle <strong>movies <\/strong>ad\u0131nda  bir tablo olu\u015ftural\u0131m<\/p>\n\n\n\n<div class=\"wp-block-urvanov-syntax-highlighter-code-block\"><pre class=\"theme:github lang:pgsql decode:true\">create  table \"Movies\"(\n    \"Rank\" int,\n    \"Title\" text,\n    \"Genre\" text,\n    \"Description\" text,\n    \"Director\" text,\n    \"Actors\" text,\n    \"Year\"  int,\n    \"Runtime (Minutes)\" int,\n    \"Rating\" decimal,\n    \"Votes\" text,\n    \"Revenue (Millions)\" text,\n    \"Metascore\" int\n);<\/pre><\/div>\n\n\n\n<p><strong>Csv <\/strong>dosyas\u0131s\u0131n\u0131 indirip ve a\u015fa\u011f\u0131daki komut ile import ederek 1000 sat\u0131rl\u0131k bir data elde etmi\u015f olacaks\u0131n\u0131z. \u0130leriki \u00e7al\u0131\u015fmalar\u0131m\u0131z bu data \u00fczerinde ger\u00e7ekle\u015fecek.<\/p>\n\n\n\n<div class=\"wp-block-file\"><a href=\"https:\/\/okankaradag.com\/wp-content\/uploads\/2021\/04\/Movies-1.csv\">Movies<\/a><a href=\"https:\/\/okankaradag.com\/wp-content\/uploads\/2021\/04\/Movies-1.csv\" class=\"wp-block-file__button\" download>\u0130ndir<\/a><\/div>\n\n\n\n<div class=\"wp-block-urvanov-syntax-highlighter-code-block\"><pre class=\"lang:pgsql decode:true\">COPY \"Movies\" FROM 'E:\\Movies.csv' DELIMITER ',' CSV HEADER;\n<\/pre><\/div>\n\n\n\n<p>\u015fimdi olu\u015fturdu\u011fumuz tabloya <strong>full text search<\/strong> yap\u0131s\u0131n\u0131 entegre edelim.<\/p>\n\n\n\n<p>Tabloya <strong>ts_vector <\/strong>tipinde bir column ve index ekleyelim. Bu s\u00fctunda aranacak anahtar kelimelerimizi saklayaca\u011f\u0131z.<\/p>\n\n\n\n<div class=\"wp-block-urvanov-syntax-highlighter-code-block\"><pre class=\"lang:pgsql decode:true\">Alter table \"Movies\" add column \"SearchKeywords\" tsvector;\nCreate Index \"GIN_SearchKeywords\" on \"Movies\" USING gin(\"SearchKeywords\");\n<\/pre><\/div>\n\n\n\n<p><strong>tsvector <\/strong>i\u00e7in farkl\u0131 s\u00f6zc\u00fck birimlerinin s\u0131ral\u0131 bir listesini saklayan data tipi diyebiliriz. Olu\u015fturdu\u011fumuz bu alana her sat\u0131r i\u00e7in aranmas\u0131 istedi\u011fimiz kelimeleri tan\u0131mlayaca\u011f\u0131z. Bu alanlar <strong>title<\/strong>, <strong>genre <\/strong>ve <strong>description <\/strong>olacak.  iste\u011fe g\u00f6re \u00e7o\u011falt\u0131labilir.<\/p>\n\n\n\n<div class=\"wp-block-urvanov-syntax-highlighter-code-block\"><pre class=\"lang:pgsql decode:true\">update \"Movies\" set \"SearchKeywords\"= to_tsvector('english',\"Title\" || ' ' || \"Genre\" || ' ' || \"Description\") where \"SearchKeywords\" is not null<\/pre><\/div>\n\n\n\n<p><strong>to_tsvector <\/strong>k\u0131sm\u0131nda yaz\u0131lan ilk parametre &#8211;<strong>english<\/strong>&#8211; postgrenin arkada belirlede\u011fi <strong>config <\/strong>tan\u0131m\u0131d\u0131r bu ayar, <strong>ingilizce <\/strong>e\u015f anlaml\u0131 ve benzer kelimeleri daha mant\u0131kl\u0131 olarak indexliyor iki e\u015f anlaml\u0131 kelimeyi ayn\u0131 alanda ayr\u0131ca tutmuyor. e\u011fer ki belirli bir dile g\u00f6re indexlemek istemiyorsan\u0131z &#8216;<strong>simple<\/strong>&#8216; configi kullanabilirsiniz.<\/p>\n\n\n\n<p>Tablomuzda h\u0131zl\u0131 arama yapabilmek i\u00e7in haz\u0131r\u0131z. <strong>postgresql <\/strong>arama yapabilmek i\u00e7in bir\u00e7ok farkl\u0131 fonksiyon deste\u011fi sunuyor. <strong>to_tsquery() <\/strong>fonksiyonu da bunlardan biri. A\u015fa\u011f\u0131daki sorgu, <strong>hero <\/strong>ile ba\u015flayan datalar\u0131 listeleyecektir. Bu fonksiyonda da config tan\u0131m\u0131n\u0131z\u0131 ayr\u0131ca belirtebilirsiniz.<\/p>\n\n\n\n<div class=\"wp-block-urvanov-syntax-highlighter-code-block\"><pre class=\"lang:pgsql decode:true\">select \"Title\" from \"Movies\" where \"SearchKeywords\" @@ to_tsquery('english','hero:*')<\/pre><\/div>\n\n\n\n<p>Bu g\u00f6nderide full text search yap\u0131s\u0131n\u0131 basit bir \u015fekilde inceledik daha detayl\u0131 incelemeler i\u00e7in postgresql&#8217;in <a href=\"https:\/\/www.postgresql.org\/docs\/9.5\/textsearch.html\" data-type=\"URL\" data-id=\"https:\/\/www.postgresql.org\/docs\/9.5\/textsearch.html\" target=\"_blank\" rel=\"noreferrer noopener\">dok\u00fcmantasyonunu <\/a>inceleyebilirsiniz<\/p>","protected":false},"excerpt":{"rendered":"<p>Merhaba, bu yaz\u0131mda postgresql full text search&#8217;\u00fcn nas\u0131l kullan\u0131ld\u0131\u011f\u0131ndan bahsedece\u011fim ve ger\u00e7ek bir tabloda nas\u0131l h\u0131zl\u0131 arama yapabilece\u011fimizi inceleyece\u011fiz. \u00d6ncelikle movies ad\u0131nda bir tablo olu\u015ftural\u0131m Csv dosyas\u0131s\u0131n\u0131 indirip ve a\u015fa\u011f\u0131daki komut ile import ederek&#46;&#46;&#46;<\/p>","protected":false},"author":1,"featured_media":238,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[41],"tags":[64,62,44,61,63],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v19.13 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>Postgresql Full Text Search Nas\u0131l Kullan\u0131l\u0131r - Okan Karada\u011f<\/title>\n<meta name=\"description\" content=\"Merhaba, bu yaz\u0131mda postgresql full text search &#039;\u00fcn nas\u0131l kullan\u0131ld\u0131\u011f\u0131ndan ve ger\u00e7ek bir tabloda nas\u0131l h\u0131zl\u0131 arama yapabilece\u011fimizi inceleyece\u011fiz.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/okankaradag.com\/en\/postgresql\/postgresql-full-text-search-nasil-kullanilir\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Postgresql Full Text Search Nas\u0131l Kullan\u0131l\u0131r - Okan Karada\u011f\" \/>\n<meta property=\"og:description\" content=\"Merhaba, bu yaz\u0131mda postgresql full text search &#039;\u00fcn nas\u0131l kullan\u0131ld\u0131\u011f\u0131ndan ve ger\u00e7ek bir tabloda nas\u0131l h\u0131zl\u0131 arama yapabilece\u011fimizi inceleyece\u011fiz.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/okankaradag.com\/en\/postgresql\/postgresql-full-text-search-nasil-kullanilir\" \/>\n<meta property=\"og:site_name\" content=\"Okan Karada\u011f\" \/>\n<meta property=\"article:published_time\" content=\"2021-04-20T14:47:17+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2021-04-20T14:54:56+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/okankaradag.com\/wp-content\/uploads\/2021\/04\/postgresql-full-text-search-e1618929777914.jpg\" \/>\n\t<meta property=\"og:image:width\" content=\"800\" \/>\n\t<meta property=\"og:image:height\" content=\"364\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/jpeg\" \/>\n<meta name=\"author\" content=\"Okan Karada\u011f\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Okan Karada\u011f\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"2 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/okankaradag.com\/en\/postgresql\/postgresql-full-text-search-nasil-kullanilir#article\",\"isPartOf\":{\"@id\":\"https:\/\/okankaradag.com\/en\/postgresql\/postgresql-full-text-search-nasil-kullanilir\"},\"author\":{\"name\":\"Okan Karada\u011f\",\"@id\":\"https:\/\/okankaradag.com\/#\/schema\/person\/0196919c5e3b6a496101ded872640d52\"},\"headline\":\"Postgresql Full Text Search Nas\u0131l Kullan\u0131l\u0131r\",\"datePublished\":\"2021-04-20T14:47:17+00:00\",\"dateModified\":\"2021-04-20T14:54:56+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/okankaradag.com\/en\/postgresql\/postgresql-full-text-search-nasil-kullanilir\"},\"wordCount\":287,\"commentCount\":0,\"publisher\":{\"@id\":\"https:\/\/okankaradag.com\/#\/schema\/person\/0196919c5e3b6a496101ded872640d52\"},\"keywords\":[\"full text search\",\"h\u0131zl\u0131 arama\",\"postgresql\",\"sql\",\"tsvector\"],\"articleSection\":[\"PostgreSql\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/okankaradag.com\/en\/postgresql\/postgresql-full-text-search-nasil-kullanilir#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/okankaradag.com\/en\/postgresql\/postgresql-full-text-search-nasil-kullanilir\",\"url\":\"https:\/\/okankaradag.com\/en\/postgresql\/postgresql-full-text-search-nasil-kullanilir\",\"name\":\"Postgresql Full Text Search Nas\u0131l Kullan\u0131l\u0131r - Okan Karada\u011f\",\"isPartOf\":{\"@id\":\"https:\/\/okankaradag.com\/#website\"},\"datePublished\":\"2021-04-20T14:47:17+00:00\",\"dateModified\":\"2021-04-20T14:54:56+00:00\",\"description\":\"Merhaba, bu yaz\u0131mda postgresql full text search '\u00fcn nas\u0131l kullan\u0131ld\u0131\u011f\u0131ndan ve ger\u00e7ek bir tabloda nas\u0131l h\u0131zl\u0131 arama yapabilece\u011fimizi inceleyece\u011fiz.\",\"breadcrumb\":{\"@id\":\"https:\/\/okankaradag.com\/en\/postgresql\/postgresql-full-text-search-nasil-kullanilir#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/okankaradag.com\/en\/postgresql\/postgresql-full-text-search-nasil-kullanilir\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/okankaradag.com\/en\/postgresql\/postgresql-full-text-search-nasil-kullanilir#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/okankaradag.com\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Postgresql Full Text Search Nas\u0131l Kullan\u0131l\u0131r\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/okankaradag.com\/#website\",\"url\":\"https:\/\/okankaradag.com\/\",\"name\":\"Okan Karada\u011f\",\"description\":\"Programlama \u00dczerine\",\"publisher\":{\"@id\":\"https:\/\/okankaradag.com\/#\/schema\/person\/0196919c5e3b6a496101ded872640d52\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/okankaradag.com\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":[\"Person\",\"Organization\"],\"@id\":\"https:\/\/okankaradag.com\/#\/schema\/person\/0196919c5e3b6a496101ded872640d52\",\"name\":\"Okan Karada\u011f\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/okankaradag.com\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/ca08a5537d7e304914c37189abedd2a1?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/ca08a5537d7e304914c37189abedd2a1?s=96&d=mm&r=g\",\"caption\":\"Okan Karada\u011f\"},\"logo\":{\"@id\":\"https:\/\/okankaradag.com\/#\/schema\/person\/image\/\"},\"sameAs\":[\"https:\/\/okankaradag.com\"],\"url\":\"https:\/\/okankaradag.com\/en\/author\/admin\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Postgresql Full Text Search Nas\u0131l Kullan\u0131l\u0131r - Okan Karada\u011f","description":"Merhaba, bu yaz\u0131mda postgresql full text search '\u00fcn nas\u0131l kullan\u0131ld\u0131\u011f\u0131ndan ve ger\u00e7ek bir tabloda nas\u0131l h\u0131zl\u0131 arama yapabilece\u011fimizi inceleyece\u011fiz.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/okankaradag.com\/en\/postgresql\/postgresql-full-text-search-nasil-kullanilir","og_locale":"en_US","og_type":"article","og_title":"Postgresql Full Text Search Nas\u0131l Kullan\u0131l\u0131r - Okan Karada\u011f","og_description":"Merhaba, bu yaz\u0131mda postgresql full text search '\u00fcn nas\u0131l kullan\u0131ld\u0131\u011f\u0131ndan ve ger\u00e7ek bir tabloda nas\u0131l h\u0131zl\u0131 arama yapabilece\u011fimizi inceleyece\u011fiz.","og_url":"https:\/\/okankaradag.com\/en\/postgresql\/postgresql-full-text-search-nasil-kullanilir","og_site_name":"Okan Karada\u011f","article_published_time":"2021-04-20T14:47:17+00:00","article_modified_time":"2021-04-20T14:54:56+00:00","og_image":[{"width":800,"height":364,"url":"https:\/\/okankaradag.com\/wp-content\/uploads\/2021\/04\/postgresql-full-text-search-e1618929777914.jpg","type":"image\/jpeg"}],"author":"Okan Karada\u011f","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Okan Karada\u011f","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/okankaradag.com\/en\/postgresql\/postgresql-full-text-search-nasil-kullanilir#article","isPartOf":{"@id":"https:\/\/okankaradag.com\/en\/postgresql\/postgresql-full-text-search-nasil-kullanilir"},"author":{"name":"Okan Karada\u011f","@id":"https:\/\/okankaradag.com\/#\/schema\/person\/0196919c5e3b6a496101ded872640d52"},"headline":"Postgresql Full Text Search Nas\u0131l Kullan\u0131l\u0131r","datePublished":"2021-04-20T14:47:17+00:00","dateModified":"2021-04-20T14:54:56+00:00","mainEntityOfPage":{"@id":"https:\/\/okankaradag.com\/en\/postgresql\/postgresql-full-text-search-nasil-kullanilir"},"wordCount":287,"commentCount":0,"publisher":{"@id":"https:\/\/okankaradag.com\/#\/schema\/person\/0196919c5e3b6a496101ded872640d52"},"keywords":["full text search","h\u0131zl\u0131 arama","postgresql","sql","tsvector"],"articleSection":["PostgreSql"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/okankaradag.com\/en\/postgresql\/postgresql-full-text-search-nasil-kullanilir#respond"]}]},{"@type":"WebPage","@id":"https:\/\/okankaradag.com\/en\/postgresql\/postgresql-full-text-search-nasil-kullanilir","url":"https:\/\/okankaradag.com\/en\/postgresql\/postgresql-full-text-search-nasil-kullanilir","name":"Postgresql Full Text Search Nas\u0131l Kullan\u0131l\u0131r - Okan Karada\u011f","isPartOf":{"@id":"https:\/\/okankaradag.com\/#website"},"datePublished":"2021-04-20T14:47:17+00:00","dateModified":"2021-04-20T14:54:56+00:00","description":"Merhaba, bu yaz\u0131mda postgresql full text search '\u00fcn nas\u0131l kullan\u0131ld\u0131\u011f\u0131ndan ve ger\u00e7ek bir tabloda nas\u0131l h\u0131zl\u0131 arama yapabilece\u011fimizi inceleyece\u011fiz.","breadcrumb":{"@id":"https:\/\/okankaradag.com\/en\/postgresql\/postgresql-full-text-search-nasil-kullanilir#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/okankaradag.com\/en\/postgresql\/postgresql-full-text-search-nasil-kullanilir"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/okankaradag.com\/en\/postgresql\/postgresql-full-text-search-nasil-kullanilir#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/okankaradag.com\/"},{"@type":"ListItem","position":2,"name":"Postgresql Full Text Search Nas\u0131l Kullan\u0131l\u0131r"}]},{"@type":"WebSite","@id":"https:\/\/okankaradag.com\/#website","url":"https:\/\/okankaradag.com\/","name":"Okan Karada\u011f","description":"Programlama \u00dczerine","publisher":{"@id":"https:\/\/okankaradag.com\/#\/schema\/person\/0196919c5e3b6a496101ded872640d52"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/okankaradag.com\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":["Person","Organization"],"@id":"https:\/\/okankaradag.com\/#\/schema\/person\/0196919c5e3b6a496101ded872640d52","name":"Okan Karada\u011f","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/okankaradag.com\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/ca08a5537d7e304914c37189abedd2a1?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/ca08a5537d7e304914c37189abedd2a1?s=96&d=mm&r=g","caption":"Okan Karada\u011f"},"logo":{"@id":"https:\/\/okankaradag.com\/#\/schema\/person\/image\/"},"sameAs":["https:\/\/okankaradag.com"],"url":"https:\/\/okankaradag.com\/en\/author\/admin"}]}},"_links":{"self":[{"href":"https:\/\/okankaradag.com\/en\/wp-json\/wp\/v2\/posts\/207"}],"collection":[{"href":"https:\/\/okankaradag.com\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/okankaradag.com\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/okankaradag.com\/en\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/okankaradag.com\/en\/wp-json\/wp\/v2\/comments?post=207"}],"version-history":[{"count":27,"href":"https:\/\/okankaradag.com\/en\/wp-json\/wp\/v2\/posts\/207\/revisions"}],"predecessor-version":[{"id":244,"href":"https:\/\/okankaradag.com\/en\/wp-json\/wp\/v2\/posts\/207\/revisions\/244"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/okankaradag.com\/en\/wp-json\/wp\/v2\/media\/238"}],"wp:attachment":[{"href":"https:\/\/okankaradag.com\/en\/wp-json\/wp\/v2\/media?parent=207"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/okankaradag.com\/en\/wp-json\/wp\/v2\/categories?post=207"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/okankaradag.com\/en\/wp-json\/wp\/v2\/tags?post=207"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}