codekingpro/portable-devtools
115k
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"><%</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">%></code> <code class="type">text</code>181 → <code class="returnvalue">boolean</code>182 </p>183 <p>184 Commutator of the <code class="literal"><%</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"><<%</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">%>></code> <code class="type">text</code>197 → <code class="returnvalue">boolean</code>198 </p>199 <p>200 Commutator of the <code class="literal"><<%</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"><-></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"><<-></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"><->></code> <code class="type">text</code>217 → <code class="returnvalue">real</code>218 </p>219 <p>220 Commutator of the <code class="literal"><<-></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"><<<-></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"><->>></code> <code class="type">text</code>230 → <code class="returnvalue">real</code>231 </p>232 <p>233 Commutator of the <code class="literal"><<<-></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"><%</code> and <code class="literal">%></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"><<%</code> and <code class="literal">%>></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 <-> '<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>' <% 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>' <<% 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>' <<-> 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>' <<<-> 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"><<a class="email" href="mailto:oleg@sai.msu.su">oleg@sai.msu.su</a>></code>, Moscow, Moscow University, Russia420 </p><p>421 Teodor Sigaev <code class="email"><<a class="email" href="mailto:teodor@sigaev.ru">teodor@sigaev.ru</a>></code>, Moscow, Delta-Soft Ltd.,Russia422 </p><p>423 Alexander Korotkov <code class="email"><<a class="email" href="mailto:a.korotkov@postgrespro.ru">a.korotkov@postgrespro.ru</a>></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>