Team Ai
Datasetpublic

codekingpro/portable-devtools

sourceHugging Faceupdated 5mo agoView on Hugging Face
1likes15kdownloads
pgtrgm.html428 linesDownload Raw Back to html
1<?xml version="1.0" encoding="UTF-8" standalone="no"?>2<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /><title>F.35. pg_trgm — support for similarity of text using trigram matching</title><link rel="stylesheet" type="text/css" href="stylesheet.css" /><link rev="made" href="pgsql-docs@lists.postgresql.org" /><meta name="generator" content="DocBook XSL Stylesheets Vsnapshot" /><link rel="prev" href="pgsurgery.html" title="F.34. pg_surgery — perform low-level surgery on relation data" /><link rel="next" href="pgvisibility.html" title="F.36. pg_visibility — visibility map information and utilities" /></head><body id="docContent" class="container-fluid col-10"><div class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">F.35. pg_trgm —3   support for similarity of text using trigram matching</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="pgsurgery.html" title="F.34. pg_surgery — perform low-level surgery on relation data">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="contrib.html" title="Appendix F. Additional Supplied Modules and Extensions">Up</a></td><th width="60%" align="center">Appendix F. Additional Supplied Modules and Extensions</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 16.3 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="pgvisibility.html" title="F.36. pg_visibility — visibility map information and utilities">Next</a></td></tr></table><hr /></div><div class="sect1" id="PGTRGM"><div class="titlepage"><div><div><h2 class="title" style="clear: both">F.35. pg_trgm —4   support for similarity of text using trigram matching <a href="#PGTRGM" class="id_link">#</a></h2></div></div></div><div class="toc"><dl class="toc"><dt><span class="sect2"><a href="pgtrgm.html#PGTRGM-CONCEPTS">F.35.1. Trigram (or Trigraph) Concepts</a></span></dt><dt><span class="sect2"><a href="pgtrgm.html#PGTRGM-FUNCS-OPS">F.35.2. Functions and Operators</a></span></dt><dt><span class="sect2"><a href="pgtrgm.html#PGTRGM-GUC">F.35.3. GUC Parameters</a></span></dt><dt><span class="sect2"><a href="pgtrgm.html#PGTRGM-INDEX">F.35.4. Index Support</a></span></dt><dt><span class="sect2"><a href="pgtrgm.html#PGTRGM-TEXT-SEARCH">F.35.5. Text Search Integration</a></span></dt><dt><span class="sect2"><a href="pgtrgm.html#PGTRGM-REFERENCES">F.35.6. References</a></span></dt><dt><span class="sect2"><a href="pgtrgm.html#PGTRGM-AUTHORS">F.35.7. Authors</a></span></dt></dl></div><a id="id-1.11.7.45.2" class="indexterm"></a><p>5  The <code class="filename">pg_trgm</code> module provides functions and operators6  for determining the similarity of7  alphanumeric text based on trigram matching, as8  well as index operator classes that support fast searching for similar9  strings.10 </p><p>11  This module is considered <span class="quote">“<span class="quote">trusted</span>”</span>, that is, it can be12  installed by non-superusers who have <code class="literal">CREATE</code> privilege13  on the current database.14 </p><div class="sect2" id="PGTRGM-CONCEPTS"><div class="titlepage"><div><div><h3 class="title">F.35.1. Trigram (or Trigraph) Concepts <a href="#PGTRGM-CONCEPTS" class="id_link">#</a></h3></div></div></div><p>15   A trigram is a group of three consecutive characters taken16   from a string.  We can measure the similarity of two strings by17   counting the number of trigrams they share.  This simple idea18   turns out to be very effective for measuring the similarity of19   words in many natural languages.20  </p><div class="note"><h3 class="title">Note</h3><p>21    <code class="filename">pg_trgm</code> ignores non-word characters22    (non-alphanumerics) when extracting trigrams from a string.23    Each word is considered to have two spaces24    prefixed and one space suffixed when determining the set25    of trigrams contained in the string.26    For example, the set of trigrams in the string27    <span class="quote">“<span class="quote"><code class="literal">cat</code></span>”</span> is28    <span class="quote">“<span class="quote"><code class="literal">  c</code></span>”</span>,29    <span class="quote">“<span class="quote"><code class="literal"> ca</code></span>”</span>,30    <span class="quote">“<span class="quote"><code class="literal">cat</code></span>”</span>, and31    <span class="quote">“<span class="quote"><code class="literal">at </code></span>”</span>.32    The set of trigrams in the string33    <span class="quote">“<span class="quote"><code class="literal">foo|bar</code></span>”</span> is34    <span class="quote">“<span class="quote"><code class="literal">  f</code></span>”</span>,35    <span class="quote">“<span class="quote"><code class="literal"> fo</code></span>”</span>,36    <span class="quote">“<span class="quote"><code class="literal">foo</code></span>”</span>,37    <span class="quote">“<span class="quote"><code class="literal">oo </code></span>”</span>,38    <span class="quote">“<span class="quote"><code class="literal">  b</code></span>”</span>,39    <span class="quote">“<span class="quote"><code class="literal"> ba</code></span>”</span>,40    <span class="quote">“<span class="quote"><code class="literal">bar</code></span>”</span>, and41    <span class="quote">“<span class="quote"><code class="literal">ar </code></span>”</span>.42   </p></div></div><div class="sect2" id="PGTRGM-FUNCS-OPS"><div class="titlepage"><div><div><h3 class="title">F.35.2. Functions and Operators <a href="#PGTRGM-FUNCS-OPS" class="id_link">#</a></h3></div></div></div><p>43   The functions provided by the <code class="filename">pg_trgm</code> module44   are shown in <a class="xref" href="pgtrgm.html#PGTRGM-FUNC-TABLE" title="Table F.26. pg_trgm Functions">Table F.26</a>, the operators45   in <a class="xref" href="pgtrgm.html#PGTRGM-OP-TABLE" title="Table F.27. pg_trgm Operators">Table F.27</a>.46  </p><div class="table" id="PGTRGM-FUNC-TABLE"><p class="title"><strong>Table F.26. <code class="filename">pg_trgm</code> Functions</strong></p><div class="table-contents"><table class="table" summary="pg_trgm Functions" border="1"><colgroup><col /></colgroup><thead><tr><th class="func_table_entry"><p class="func_signature">47        Function48       </p>49       <p>50        Description51       </p></th></tr></thead><tbody><tr><td class="func_table_entry"><p class="func_signature">52        <a id="id-1.11.7.45.6.3.2.2.1.1.1.1" class="indexterm"></a>53        <code class="function">similarity</code> ( <code class="type">text</code>, <code class="type">text</code> )54        → <code class="returnvalue">real</code>55       </p>56       <p>57        Returns a number that indicates how similar the two arguments are.58        The range of the result is zero (indicating that the two strings are59        completely dissimilar) to one (indicating that the two strings are60        identical).61       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">62        <a id="id-1.11.7.45.6.3.2.2.2.1.1.1" class="indexterm"></a>63        <code class="function">show_trgm</code> ( <code class="type">text</code> )64        → <code class="returnvalue">text[]</code>65       </p>66       <p>67        Returns an array of all the trigrams in the given string.68        (In practice this is seldom useful except for debugging.)69       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">70        <a id="id-1.11.7.45.6.3.2.2.3.1.1.1" class="indexterm"></a>71        <code class="function">word_similarity</code> ( <code class="type">text</code>, <code class="type">text</code> )72        → <code class="returnvalue">real</code>73       </p>74       <p>75        Returns a number that indicates the greatest similarity between76        the set of trigrams in the first string and any continuous extent77        of an ordered set of trigrams in the second string.  For details, see78        the explanation below.79       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">80        <a id="id-1.11.7.45.6.3.2.2.4.1.1.1" class="indexterm"></a>81        <code class="function">strict_word_similarity</code> ( <code class="type">text</code>, <code class="type">text</code> )82        → <code class="returnvalue">real</code>83       </p>84       <p>85        Same as <code class="function">word_similarity</code>, but forces86        extent boundaries to match word boundaries.  Since we don't have87        cross-word trigrams, this function actually returns greatest similarity88        between first string and any continuous extent of words of the second89        string.90       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">91        <a id="id-1.11.7.45.6.3.2.2.5.1.1.1" class="indexterm"></a>92        <code class="function">show_limit</code> ()93        → <code class="returnvalue">real</code>94       </p>95       <p>96        Returns the current similarity threshold used by the <code class="literal">%</code>97        operator.  This sets the minimum similarity between98        two words for them to be considered similar enough to99        be misspellings of each other, for example.100        (<span class="emphasis"><em>Deprecated</em></span>; instead use <code class="command">SHOW</code>101        <code class="varname">pg_trgm.similarity_threshold</code>.)102       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">103        <a id="id-1.11.7.45.6.3.2.2.6.1.1.1" class="indexterm"></a>104        <code class="function">set_limit</code> ( <code class="type">real</code> )105        → <code class="returnvalue">real</code>106       </p>107       <p>108        Sets the current similarity threshold that is used by the <code class="literal">%</code>109        operator.  The threshold must be between 0 and 1 (default is 0.3).110        Returns the same value passed in.111        (<span class="emphasis"><em>Deprecated</em></span>; instead use <code class="command">SET</code>112        <code class="varname">pg_trgm.similarity_threshold</code>.)113       </p></td></tr></tbody></table></div></div><br class="table-break" /><p>114   Consider the following example:115 116</p><pre class="programlisting">117# SELECT word_similarity('word', 'two words');118 word_similarity119-----------------120             0.8121(1 row)122</pre><p>123 124   In the first string, the set of trigrams is125   <code class="literal">{"  w"," wo","wor","ord","rd "}</code>.126   In the second string, the ordered set of trigrams is127   <code class="literal">{"  t"," tw","two","wo ","  w"," wo","wor","ord","rds","ds "}</code>.128   The most similar extent of an ordered set of trigrams in the second string129   is <code class="literal">{"  w"," wo","wor","ord"}</code>, and the similarity is130   <code class="literal">0.8</code>.131  </p><p>132   This function returns a value that can be approximately understood as the133   greatest similarity between the first string and any substring of the second134   string.  However, this function does not add padding to the boundaries of135   the extent.  Thus, the number of additional characters present in the136   second string is not considered, except for the mismatched word boundaries.137  </p><p>138   At the same time, <code class="function">strict_word_similarity</code>139   selects an extent of words in the second string.  In the example above,140   <code class="function">strict_word_similarity</code> would select the141   extent of a single word <code class="literal">'words'</code>, whose set of trigrams is142   <code class="literal">{"  w"," wo","wor","ord","rds","ds "}</code>.143 144</p><pre class="programlisting">145# SELECT strict_word_similarity('word', 'two words'), similarity('word', 'words');146 strict_word_similarity | similarity147------------------------+------------148               0.571429 |   0.571429149(1 row)150</pre><p>151  </p><p>152   Thus, the <code class="function">strict_word_similarity</code> function153   is useful for finding the similarity to whole words, while154   <code class="function">word_similarity</code> is more suitable for155   finding the similarity for parts of words.156  </p><div class="table" id="PGTRGM-OP-TABLE"><p class="title"><strong>Table F.27. <code class="filename">pg_trgm</code> Operators</strong></p><div class="table-contents"><table class="table" summary="pg_trgm Operators" border="1"><colgroup><col /></colgroup><thead><tr><th class="func_table_entry"><p class="func_signature">157        Operator158       </p>159       <p>160        Description161       </p></th></tr></thead><tbody><tr><td class="func_table_entry"><p class="func_signature">162        <code class="type">text</code> <code class="literal">%</code> <code class="type">text</code>163        → <code class="returnvalue">boolean</code>164       </p>165       <p>166        Returns <code class="literal">true</code> if its arguments have a similarity167        that is greater than the current similarity threshold set by168        <code class="varname">pg_trgm.similarity_threshold</code>.169       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">170        <code class="type">text</code> <code class="literal">&lt;%</code> <code class="type">text</code>171        → <code class="returnvalue">boolean</code>172       </p>173       <p>174        Returns <code class="literal">true</code> if the similarity between the trigram175        set in the first argument and a continuous extent of an ordered trigram176        set in the second argument is greater than the current word similarity177        threshold set by <code class="varname">pg_trgm.word_similarity_threshold</code>178        parameter.179       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">180        <code class="type">text</code> <code class="literal">%&gt;</code> <code class="type">text</code>181        → <code class="returnvalue">boolean</code>182       </p>183       <p>184        Commutator of the <code class="literal">&lt;%</code> operator.185       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">186        <code class="type">text</code> <code class="literal">&lt;&lt;%</code> <code class="type">text</code>187        → <code class="returnvalue">boolean</code>188       </p>189       <p>190        Returns <code class="literal">true</code> if its second argument has a continuous191        extent of an ordered trigram set that matches word boundaries,192        and its similarity to the trigram set of the first argument is greater193        than the current strict word similarity threshold set by the194        <code class="varname">pg_trgm.strict_word_similarity_threshold</code> parameter.195       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">196        <code class="type">text</code> <code class="literal">%&gt;&gt;</code> <code class="type">text</code>197        → <code class="returnvalue">boolean</code>198       </p>199       <p>200        Commutator of the <code class="literal">&lt;&lt;%</code> operator.201       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">202        <code class="type">text</code> <code class="literal">&lt;-&gt;</code> <code class="type">text</code>203        → <code class="returnvalue">real</code>204       </p>205       <p>206        Returns the <span class="quote">“<span class="quote">distance</span>”</span> between the arguments, that is207        one minus the <code class="function">similarity()</code> value.208       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">209        <code class="type">text</code> <code class="literal">&lt;&lt;-&gt;</code> <code class="type">text</code>210        → <code class="returnvalue">real</code>211       </p>212       <p>213        Returns the <span class="quote">“<span class="quote">distance</span>”</span> between the arguments, that is214        one minus the <code class="function">word_similarity()</code> value.215       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">216        <code class="type">text</code> <code class="literal">&lt;-&gt;&gt;</code> <code class="type">text</code>217        → <code class="returnvalue">real</code>218       </p>219       <p>220        Commutator of the <code class="literal">&lt;&lt;-&gt;</code> operator.221       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">222        <code class="type">text</code> <code class="literal">&lt;&lt;&lt;-&gt;</code> <code class="type">text</code>223        → <code class="returnvalue">real</code>224       </p>225       <p>226        Returns the <span class="quote">“<span class="quote">distance</span>”</span> between the arguments, that is227        one minus the <code class="function">strict_word_similarity()</code> value.228       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">229        <code class="type">text</code> <code class="literal">&lt;-&gt;&gt;&gt;</code> <code class="type">text</code>230        → <code class="returnvalue">real</code>231       </p>232       <p>233        Commutator of the <code class="literal">&lt;&lt;&lt;-&gt;</code> operator.234       </p></td></tr></tbody></table></div></div><br class="table-break" /></div><div class="sect2" id="PGTRGM-GUC"><div class="titlepage"><div><div><h3 class="title">F.35.3. GUC Parameters <a href="#PGTRGM-GUC" class="id_link">#</a></h3></div></div></div><div class="variablelist"><dl class="variablelist"><dt id="GUC-PGTRGM-SIMILARITY-THRESHOLD"><span class="term">235     <code class="varname">pg_trgm.similarity_threshold</code> (<code class="type">real</code>)236     <a id="id-1.11.7.45.7.2.1.1.3" class="indexterm"></a>237    </span> <a href="#GUC-PGTRGM-SIMILARITY-THRESHOLD" class="id_link">#</a></dt><dd><p>238      Sets the current similarity threshold that is used by the <code class="literal">%</code>239      operator.  The threshold must be between 0 and 1 (default is 0.3).240     </p></dd><dt id="GUC-PGTRGM-WORD-SIMILARITY-THRESHOLD"><span class="term">241     <code class="varname">pg_trgm.word_similarity_threshold</code> (<code class="type">real</code>)242     <a id="id-1.11.7.45.7.2.2.1.3" class="indexterm"></a>243    </span> <a href="#GUC-PGTRGM-WORD-SIMILARITY-THRESHOLD" class="id_link">#</a></dt><dd><p>244      Sets the current word similarity threshold that is used by the245      <code class="literal">&lt;%</code> and <code class="literal">%&gt;</code> operators.  The threshold246      must be between 0 and 1 (default is 0.6).247     </p></dd><dt id="GUC-PGTRGM-STRICT-WORD-SIMILARITY-THRESHOLD"><span class="term">248     <code class="varname">pg_trgm.strict_word_similarity_threshold</code> (<code class="type">real</code>)249     <a id="id-1.11.7.45.7.2.3.1.3" class="indexterm"></a>250    </span> <a href="#GUC-PGTRGM-STRICT-WORD-SIMILARITY-THRESHOLD" class="id_link">#</a></dt><dd><p>251      Sets the current strict word similarity threshold that is used by the252      <code class="literal">&lt;&lt;%</code> and <code class="literal">%&gt;&gt;</code> operators.  The threshold253      must be between 0 and 1 (default is 0.5).254     </p></dd></dl></div></div><div class="sect2" id="PGTRGM-INDEX"><div class="titlepage"><div><div><h3 class="title">F.35.4. Index Support <a href="#PGTRGM-INDEX" class="id_link">#</a></h3></div></div></div><p>255   The <code class="filename">pg_trgm</code> module provides GiST and GIN index256   operator classes that allow you to create an index over a text column for257   the purpose of very fast similarity searches.  These index types support258   the above-described similarity operators, and additionally support259   trigram-based index searches for <code class="literal">LIKE</code>, <code class="literal">ILIKE</code>,260   <code class="literal">~</code>, <code class="literal">~*</code> and <code class="literal">=</code> queries.261   The similarity comparisons are case-insensitive in a default build of262   <code class="filename">pg_trgm</code>.263   Inequality operators are not supported.264   Note that those indexes may not be as efficient as regular B-tree indexes265   for equality operator.266  </p><p>267   Example:268 269</p><pre class="programlisting">270CREATE TABLE test_trgm (t text);271CREATE INDEX trgm_idx ON test_trgm USING GIST (t gist_trgm_ops);272</pre><p>273or274</p><pre class="programlisting">275CREATE INDEX trgm_idx ON test_trgm USING GIN (t gin_trgm_ops);276</pre><p>277  </p><p>278   <code class="literal">gist_trgm_ops</code> GiST opclass approximates a set of279   trigrams as a bitmap signature.  Its optional integer parameter280   <code class="literal">siglen</code> determines the281   signature length in bytes.  The default length is 12 bytes.282   Valid values of signature length are between 1 and 2024 bytes.  Longer283   signatures lead to a more precise search (scanning a smaller fraction of the index and284   fewer heap pages), at the cost of a larger index.285  </p><p>286   Example of creating such an index with a signature length of 32 bytes:287  </p><pre class="programlisting">288CREATE INDEX trgm_idx ON test_trgm USING GIST (t gist_trgm_ops(siglen=32));289</pre><p>290   At this point, you will have an index on the <code class="structfield">t</code> column that291   you can use for similarity searching.  A typical query is292</p><pre class="programlisting">293SELECT t, similarity(t, '<em class="replaceable"><code>word</code></em>') AS sml294  FROM test_trgm295  WHERE t % '<em class="replaceable"><code>word</code></em>'296  ORDER BY sml DESC, t;297</pre><p>298   This will return all values in the text column that are sufficiently299   similar to <em class="replaceable"><code>word</code></em>, sorted from best match to worst.  The300   index will be used to make this a fast operation even over very large data301   sets.302  </p><p>303   A variant of the above query is304</p><pre class="programlisting">305SELECT t, t &lt;-&gt; '<em class="replaceable"><code>word</code></em>' AS dist306  FROM test_trgm307  ORDER BY dist LIMIT 10;308</pre><p>309   This can be implemented quite efficiently by GiST indexes, but not310   by GIN indexes.  It will usually beat the first formulation when only311   a small number of the closest matches is wanted.312  </p><p>313   Also you can use an index on the <code class="structfield">t</code> column for word314   similarity or strict word similarity.  Typical queries are:315</p><pre class="programlisting">316SELECT t, word_similarity('<em class="replaceable"><code>word</code></em>', t) AS sml317  FROM test_trgm318  WHERE '<em class="replaceable"><code>word</code></em>' &lt;% t319  ORDER BY sml DESC, t;320</pre><p>321   and322</p><pre class="programlisting">323SELECT t, strict_word_similarity('<em class="replaceable"><code>word</code></em>', t) AS sml324  FROM test_trgm325  WHERE '<em class="replaceable"><code>word</code></em>' &lt;&lt;% t326  ORDER BY sml DESC, t;327</pre><p>328   This will return all values in the text column for which there is a329   continuous extent in the corresponding ordered trigram set that is330   sufficiently similar to the trigram set of <em class="replaceable"><code>word</code></em>,331   sorted from best match to worst.  The index will be used to make this332   a fast operation even over very large data sets.333  </p><p>334   Possible variants of the above queries are:335</p><pre class="programlisting">336SELECT t, '<em class="replaceable"><code>word</code></em>' &lt;&lt;-&gt; t AS dist337  FROM test_trgm338  ORDER BY dist LIMIT 10;339</pre><p>340   and341</p><pre class="programlisting">342SELECT t, '<em class="replaceable"><code>word</code></em>' &lt;&lt;&lt;-&gt; t AS dist343  FROM test_trgm344  ORDER BY dist LIMIT 10;345</pre><p>346   This can be implemented quite efficiently by GiST indexes, but not347   by GIN indexes.348  </p><p>349   Beginning in <span class="productname">PostgreSQL</span> 9.1, these index types also support350   index searches for <code class="literal">LIKE</code> and <code class="literal">ILIKE</code>, for example351</p><pre class="programlisting">352SELECT * FROM test_trgm WHERE t LIKE '%foo%bar';353</pre><p>354   The index search works by extracting trigrams from the search string355   and then looking these up in the index.  The more trigrams in the search356   string, the more effective the index search is.  Unlike B-tree based357   searches, the search string need not be left-anchored.358  </p><p>359   Beginning in <span class="productname">PostgreSQL</span> 9.3, these index types also support360   index searches for regular-expression matches361   (<code class="literal">~</code> and <code class="literal">~*</code> operators), for example362</p><pre class="programlisting">363SELECT * FROM test_trgm WHERE t ~ '(foo|bar)';364</pre><p>365   The index search works by extracting trigrams from the regular expression366   and then looking these up in the index.  The more trigrams that can be367   extracted from the regular expression, the more effective the index search368   is.  Unlike B-tree based searches, the search string need not be369   left-anchored.370  </p><p>371   For both <code class="literal">LIKE</code> and regular-expression searches, keep in mind372   that a pattern with no extractable trigrams will degenerate to a full-index373   scan.374  </p><p>375   The choice between GiST and GIN indexing depends on the relative376   performance characteristics of GiST and GIN, which are discussed elsewhere.377  </p></div><div class="sect2" id="PGTRGM-TEXT-SEARCH"><div class="titlepage"><div><div><h3 class="title">F.35.5. Text Search Integration <a href="#PGTRGM-TEXT-SEARCH" class="id_link">#</a></h3></div></div></div><p>378   Trigram matching is a very useful tool when used in conjunction379   with a full text index.  In particular it can help to recognize380   misspelled input words that will not be matched directly by the381   full text search mechanism.382  </p><p>383   The first step is to generate an auxiliary table containing all384   the unique words in the documents:385 386</p><pre class="programlisting">387CREATE TABLE words AS SELECT word FROM388        ts_stat('SELECT to_tsvector(''simple'', bodytext) FROM documents');389</pre><p>390 391   where <code class="structname">documents</code> is a table that has a text field392   <code class="structfield">bodytext</code> that we wish to search.  The reason for using393   the <code class="literal">simple</code> configuration with the <code class="function">to_tsvector</code>394   function, instead of using a language-specific configuration,395   is that we want a list of the original (unstemmed) words.396  </p><p>397   Next, create a trigram index on the word column:398 399</p><pre class="programlisting">400CREATE INDEX words_idx ON words USING GIN (word gin_trgm_ops);401</pre><p>402 403   Now, a <code class="command">SELECT</code> query similar to the previous example can404   be used to suggest spellings for misspelled words in user search terms.405   A useful extra test is to require that the selected words are also of406   similar length to the misspelled word.407  </p><div class="note"><h3 class="title">Note</h3><p>408    Since the <code class="structname">words</code> table has been generated as a separate,409    static table, it will need to be periodically regenerated so that410    it remains reasonably up-to-date with the document collection.411    Keeping it exactly current is usually unnecessary.412   </p></div></div><div class="sect2" id="PGTRGM-REFERENCES"><div class="titlepage"><div><div><h3 class="title">F.35.6. References <a href="#PGTRGM-REFERENCES" class="id_link">#</a></h3></div></div></div><p>413   GiST Development Site414   <a class="ulink" href="http://www.sai.msu.su/~megera/postgres/gist/" target="_top">http://www.sai.msu.su/~megera/postgres/gist/</a>415  </p><p>416   Tsearch2 Development Site417   <a class="ulink" href="http://www.sai.msu.su/~megera/postgres/gist/tsearch/V2/" target="_top">http://www.sai.msu.su/~megera/postgres/gist/tsearch/V2/</a>418  </p></div><div class="sect2" id="PGTRGM-AUTHORS"><div class="titlepage"><div><div><h3 class="title">F.35.7. Authors <a href="#PGTRGM-AUTHORS" class="id_link">#</a></h3></div></div></div><p>419   Oleg Bartunov <code class="email">&lt;<a class="email" href="mailto:oleg@sai.msu.su">oleg@sai.msu.su</a>&gt;</code>, Moscow, Moscow University, Russia420  </p><p>421   Teodor Sigaev <code class="email">&lt;<a class="email" href="mailto:teodor@sigaev.ru">teodor@sigaev.ru</a>&gt;</code>, Moscow, Delta-Soft Ltd.,Russia422  </p><p>423   Alexander Korotkov <code class="email">&lt;<a class="email" href="mailto:a.korotkov@postgrespro.ru">a.korotkov@postgrespro.ru</a>&gt;</code>, Moscow, Postgres Professional, Russia424  </p><p>425   Documentation: Christopher Kings-Lynne426  </p><p>427   This module is sponsored by Delta-Soft Ltd., Moscow, Russia.428  </p></div></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="pgsurgery.html" title="F.34. pg_surgery — perform low-level surgery on relation data">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="contrib.html" title="Appendix F. Additional Supplied Modules and Extensions">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="pgvisibility.html" title="F.36. pg_visibility — visibility map information and utilities">Next</a></td></tr><tr><td width="40%" align="left" valign="top">F.34. pg_surgery — perform low-level surgery on relation data </td><td width="20%" align="center"><a accesskey="h" href="index.html" title="PostgreSQL 16.3 Documentation">Home</a></td><td width="40%" align="right" valign="top"> F.36. pg_visibility — visibility map information and utilities</td></tr></table></div></body></html>