Team Ai
Datasetpublic

codekingpro/portable-devtools

sourceHugging Faceupdated 5mo agoView on Hugging Face
1likes15kdownloads
functions-textsearch.html763 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>9.13. Text Search Functions and Operators</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="functions-net.html" title="9.12. Network Address Functions and Operators" /><link rel="next" href="functions-uuid.html" title="9.14. UUID Functions" /></head><body id="docContent" class="container-fluid col-10"><div class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">9.13. Text Search Functions and Operators</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="functions-net.html" title="9.12. Network Address Functions and Operators">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="functions.html" title="Chapter 9. Functions and Operators">Up</a></td><th width="60%" align="center">Chapter 9. Functions and Operators</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="functions-uuid.html" title="9.14. UUID Functions">Next</a></td></tr></table><hr /></div><div class="sect1" id="FUNCTIONS-TEXTSEARCH"><div class="titlepage"><div><div><h2 class="title" style="clear: both">9.13. Text Search Functions and Operators <a href="#FUNCTIONS-TEXTSEARCH" class="id_link">#</a></h2></div></div></div><a id="id-1.5.8.19.2" class="indexterm"></a><a id="id-1.5.8.19.3" class="indexterm"></a><p>3   <a class="xref" href="functions-textsearch.html#TEXTSEARCH-OPERATORS-TABLE" title="Table 9.42. Text Search Operators">Table 9.42</a>,4   <a class="xref" href="functions-textsearch.html#TEXTSEARCH-FUNCTIONS-TABLE" title="Table 9.43. Text Search Functions">Table 9.43</a> and5   <a class="xref" href="functions-textsearch.html#TEXTSEARCH-FUNCTIONS-DEBUG-TABLE" title="Table 9.44. Text Search Debugging Functions">Table 9.44</a>6   summarize the functions and operators that are provided7   for full text searching.  See <a class="xref" href="textsearch.html" title="Chapter 12. Full Text Search">Chapter 12</a> for a detailed8   explanation of <span class="productname">PostgreSQL</span>'s text search9   facility.10  </p><div class="table" id="TEXTSEARCH-OPERATORS-TABLE"><p class="title"><strong>Table 9.42. Text Search Operators</strong></p><div class="table-contents"><table class="table" summary="Text Search Operators" border="1"><colgroup><col /></colgroup><thead><tr><th class="func_table_entry"><p class="func_signature">11        Operator12       </p>13       <p>14        Description15       </p>16       <p>17        Example(s)18       </p></th></tr></thead><tbody><tr><td class="func_table_entry"><p class="func_signature">19        <code class="type">tsvector</code> <code class="literal">@@</code> <code class="type">tsquery</code>20        → <code class="returnvalue">boolean</code>21       </p>22       <p class="func_signature">23        <code class="type">tsquery</code> <code class="literal">@@</code> <code class="type">tsvector</code>24        → <code class="returnvalue">boolean</code>25       </p>26       <p>27        Does <code class="type">tsvector</code> match <code class="type">tsquery</code>?28        (The arguments can be given in either order.)29       </p>30       <p>31        <code class="literal">to_tsvector('fat cats ate rats') @@ to_tsquery('cat &amp; rat')</code>32        → <code class="returnvalue">t</code>33       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">34        <code class="type">text</code> <code class="literal">@@</code> <code class="type">tsquery</code>35        → <code class="returnvalue">boolean</code>36       </p>37       <p>38        Does text string, after implicit invocation39        of <code class="function">to_tsvector()</code>, match <code class="type">tsquery</code>?40       </p>41       <p>42        <code class="literal">'fat cats ate rats' @@ to_tsquery('cat &amp; rat')</code>43        → <code class="returnvalue">t</code>44       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">45        <code class="type">tsvector</code> <code class="literal">@@@</code> <code class="type">tsquery</code>46        → <code class="returnvalue">boolean</code>47       </p>48       <p class="func_signature">49        <code class="type">tsquery</code> <code class="literal">@@@</code> <code class="type">tsvector</code>50        → <code class="returnvalue">boolean</code>51       </p>52       <p>53        This is a deprecated synonym for <code class="literal">@@</code>.54       </p>55       <p>56        <code class="literal">to_tsvector('fat cats ate rats') @@@ to_tsquery('cat &amp; rat')</code>57        → <code class="returnvalue">t</code>58       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">59        <code class="type">tsvector</code> <code class="literal">||</code> <code class="type">tsvector</code>60        → <code class="returnvalue">tsvector</code>61       </p>62       <p>63        Concatenates two <code class="type">tsvector</code>s.  If both inputs contain64        lexeme positions, the second input's positions are adjusted65        accordingly.66       </p>67       <p>68        <code class="literal">'a:1 b:2'::tsvector || 'c:1 d:2 b:3'::tsvector</code>69        → <code class="returnvalue">'a':1 'b':2,5 'c':3 'd':4</code>70       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">71        <code class="type">tsquery</code> <code class="literal">&amp;&amp;</code> <code class="type">tsquery</code>72        → <code class="returnvalue">tsquery</code>73       </p>74       <p>75        ANDs two <code class="type">tsquery</code>s together, producing a query that76        matches documents that match both input queries.77       </p>78       <p>79        <code class="literal">'fat | rat'::tsquery &amp;&amp; 'cat'::tsquery</code>80        → <code class="returnvalue">( 'fat' | 'rat' ) &amp; 'cat'</code>81       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">82        <code class="type">tsquery</code> <code class="literal">||</code> <code class="type">tsquery</code>83        → <code class="returnvalue">tsquery</code>84       </p>85       <p>86        ORs two <code class="type">tsquery</code>s together, producing a query that87        matches documents that match either input query.88       </p>89       <p>90        <code class="literal">'fat | rat'::tsquery || 'cat'::tsquery</code>91        → <code class="returnvalue">'fat' | 'rat' | 'cat'</code>92       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">93        <code class="literal">!!</code> <code class="type">tsquery</code>94        → <code class="returnvalue">tsquery</code>95       </p>96       <p>97        Negates a <code class="type">tsquery</code>, producing a query that matches98        documents that do not match the input query.99       </p>100       <p>101        <code class="literal">!! 'cat'::tsquery</code>102        → <code class="returnvalue">!'cat'</code>103       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">104        <code class="type">tsquery</code> <code class="literal">&lt;-&gt;</code> <code class="type">tsquery</code>105        → <code class="returnvalue">tsquery</code>106       </p>107       <p>108        Constructs a phrase query, which matches if the two input queries109        match at successive lexemes.110       </p>111       <p>112        <code class="literal">to_tsquery('fat') &lt;-&gt; to_tsquery('rat')</code>113        → <code class="returnvalue">'fat' &lt;-&gt; 'rat'</code>114       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">115        <code class="type">tsquery</code> <code class="literal">@&gt;</code> <code class="type">tsquery</code>116        → <code class="returnvalue">boolean</code>117       </p>118       <p>119        Does first <code class="type">tsquery</code> contain the second?  (This considers120        only whether all the lexemes appearing in one query appear in the121        other, ignoring the combining operators.)122       </p>123       <p>124        <code class="literal">'cat'::tsquery @&gt; 'cat &amp; rat'::tsquery</code>125        → <code class="returnvalue">f</code>126       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">127        <code class="type">tsquery</code> <code class="literal">&lt;@</code> <code class="type">tsquery</code>128        → <code class="returnvalue">boolean</code>129       </p>130       <p>131        Is first <code class="type">tsquery</code> contained in the second?  (This132        considers only whether all the lexemes appearing in one query appear133        in the other, ignoring the combining operators.)134       </p>135       <p>136        <code class="literal">'cat'::tsquery &lt;@ 'cat &amp; rat'::tsquery</code>137        → <code class="returnvalue">t</code>138       </p>139       <p>140        <code class="literal">'cat'::tsquery &lt;@ '!cat &amp; rat'::tsquery</code>141        → <code class="returnvalue">t</code>142       </p></td></tr></tbody></table></div></div><br class="table-break" /><p>143     In addition to these specialized operators, the usual comparison144     operators shown in <a class="xref" href="functions-comparison.html#FUNCTIONS-COMPARISON-OP-TABLE" title="Table 9.1. Comparison Operators">Table 9.1</a> are145     available for types <code class="type">tsvector</code> and <code class="type">tsquery</code>.146     These are not very147     useful for text searching but allow, for example, unique indexes to be148     built on columns of these types.149    </p><div class="table" id="TEXTSEARCH-FUNCTIONS-TABLE"><p class="title"><strong>Table 9.43. Text Search Functions</strong></p><div class="table-contents"><table class="table" summary="Text Search Functions" border="1"><colgroup><col /></colgroup><thead><tr><th class="func_table_entry"><p class="func_signature">150        Function151       </p>152       <p>153        Description154       </p>155       <p>156        Example(s)157       </p></th></tr></thead><tbody><tr><td class="func_table_entry"><p class="func_signature">158        <a id="id-1.5.8.19.7.2.2.1.1.1.1" class="indexterm"></a>159        <code class="function">array_to_tsvector</code> ( <code class="type">text[]</code> )160        → <code class="returnvalue">tsvector</code>161       </p>162       <p>163        Converts an array of text strings to a <code class="type">tsvector</code>.164        The given strings are used as lexemes as-is, without further165        processing.  Array elements must not be empty strings166        or <code class="literal">NULL</code>.167       </p>168       <p>169        <code class="literal">array_to_tsvector('{fat,cat,rat}'::text[])</code>170        → <code class="returnvalue">'cat' 'fat' 'rat'</code>171       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">172        <a id="id-1.5.8.19.7.2.2.2.1.1.1" class="indexterm"></a>173        <code class="function">get_current_ts_config</code> ( )174        → <code class="returnvalue">regconfig</code>175       </p>176       <p>177        Returns the OID of the current default text search configuration178        (as set by <a class="xref" href="runtime-config-client.html#GUC-DEFAULT-TEXT-SEARCH-CONFIG">default_text_search_config</a>).179       </p>180       <p>181        <code class="literal">get_current_ts_config()</code>182        → <code class="returnvalue">english</code>183       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">184        <a id="id-1.5.8.19.7.2.2.3.1.1.1" class="indexterm"></a>185        <code class="function">length</code> ( <code class="type">tsvector</code> )186        → <code class="returnvalue">integer</code>187       </p>188       <p>189        Returns the number of lexemes in the <code class="type">tsvector</code>.190       </p>191       <p>192        <code class="literal">length('fat:2,4 cat:3 rat:5A'::tsvector)</code>193        → <code class="returnvalue">3</code>194       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">195        <a id="id-1.5.8.19.7.2.2.4.1.1.1" class="indexterm"></a>196        <code class="function">numnode</code> ( <code class="type">tsquery</code> )197        → <code class="returnvalue">integer</code>198       </p>199       <p>200        Returns the number of lexemes plus operators in201        the <code class="type">tsquery</code>.202       </p>203       <p>204        <code class="literal">numnode('(fat &amp; rat) | cat'::tsquery)</code>205        → <code class="returnvalue">5</code>206       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">207        <a id="id-1.5.8.19.7.2.2.5.1.1.1" class="indexterm"></a>208        <code class="function">plainto_tsquery</code> (209        [<span class="optional"> <em class="parameter"><code>config</code></em> <code class="type">regconfig</code>, </span>]210        <em class="parameter"><code>query</code></em> <code class="type">text</code> )211        → <code class="returnvalue">tsquery</code>212       </p>213       <p>214        Converts text to a <code class="type">tsquery</code>, normalizing words according to215        the specified or default configuration.  Any punctuation in the string216        is ignored (it does not determine query operators).  The resulting217        query matches documents containing all non-stopwords in the text.218       </p>219       <p>220        <code class="literal">plainto_tsquery('english', 'The Fat Rats')</code>221        → <code class="returnvalue">'fat' &amp; 'rat'</code>222       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">223        <a id="id-1.5.8.19.7.2.2.6.1.1.1" class="indexterm"></a>224        <code class="function">phraseto_tsquery</code> (225        [<span class="optional"> <em class="parameter"><code>config</code></em> <code class="type">regconfig</code>, </span>]226        <em class="parameter"><code>query</code></em> <code class="type">text</code> )227        → <code class="returnvalue">tsquery</code>228       </p>229       <p>230        Converts text to a <code class="type">tsquery</code>, normalizing words according to231        the specified or default configuration.  Any punctuation in the string232        is ignored (it does not determine query operators).  The resulting233        query matches phrases containing all non-stopwords in the text.234       </p>235       <p>236        <code class="literal">phraseto_tsquery('english', 'The Fat Rats')</code>237        → <code class="returnvalue">'fat' &lt;-&gt; 'rat'</code>238       </p>239       <p>240        <code class="literal">phraseto_tsquery('english', 'The Cat and Rats')</code>241        → <code class="returnvalue">'cat' &lt;2&gt; 'rat'</code>242       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">243        <a id="id-1.5.8.19.7.2.2.7.1.1.1" class="indexterm"></a>244        <code class="function">websearch_to_tsquery</code> (245        [<span class="optional"> <em class="parameter"><code>config</code></em> <code class="type">regconfig</code>, </span>]246        <em class="parameter"><code>query</code></em> <code class="type">text</code> )247        → <code class="returnvalue">tsquery</code>248       </p>249       <p>250        Converts text to a <code class="type">tsquery</code>, normalizing words according251        to the specified or default configuration.  Quoted word sequences are252        converted to phrase tests.  The word <span class="quote">“<span class="quote">or</span>”</span> is understood253        as producing an OR operator, and a dash produces a NOT operator;254        other punctuation is ignored.255        This approximates the behavior of some common web search tools.256       </p>257       <p>258        <code class="literal">websearch_to_tsquery('english', '"fat rat" or cat dog')</code>259        → <code class="returnvalue">'fat' &lt;-&gt; 'rat' | 'cat' &amp; 'dog'</code>260       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">261        <a id="id-1.5.8.19.7.2.2.8.1.1.1" class="indexterm"></a>262        <code class="function">querytree</code> ( <code class="type">tsquery</code> )263        → <code class="returnvalue">text</code>264       </p>265       <p>266        Produces a representation of the indexable portion of267        a <code class="type">tsquery</code>.  A result that is empty or268        just <code class="literal">T</code> indicates a non-indexable query.269       </p>270       <p>271        <code class="literal">querytree('foo &amp; ! bar'::tsquery)</code>272        → <code class="returnvalue">'foo'</code>273       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">274        <a id="id-1.5.8.19.7.2.2.9.1.1.1" class="indexterm"></a>275        <code class="function">setweight</code> ( <em class="parameter"><code>vector</code></em> <code class="type">tsvector</code>, <em class="parameter"><code>weight</code></em> <code class="type">"char"</code> )276        → <code class="returnvalue">tsvector</code>277       </p>278       <p>279        Assigns the specified <em class="parameter"><code>weight</code></em> to each element280        of the <em class="parameter"><code>vector</code></em>.281       </p>282       <p>283        <code class="literal">setweight('fat:2,4 cat:3 rat:5B'::tsvector, 'A')</code>284        → <code class="returnvalue">'cat':3A 'fat':2A,4A 'rat':5A</code>285       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">286        <a id="id-1.5.8.19.7.2.2.10.1.1.1" class="indexterm"></a>287        <code class="function">setweight</code> ( <em class="parameter"><code>vector</code></em> <code class="type">tsvector</code>, <em class="parameter"><code>weight</code></em> <code class="type">"char"</code>, <em class="parameter"><code>lexemes</code></em> <code class="type">text[]</code> )288        → <code class="returnvalue">tsvector</code>289       </p>290       <p>291        Assigns the specified <em class="parameter"><code>weight</code></em> to elements292        of the <em class="parameter"><code>vector</code></em> that are listed293        in <em class="parameter"><code>lexemes</code></em>.294        The strings in <em class="parameter"><code>lexemes</code></em> are taken as lexemes295        as-is, without further processing.  Strings that do not match any296        lexeme in <em class="parameter"><code>vector</code></em> are ignored.297       </p>298       <p>299        <code class="literal">setweight('fat:2,4 cat:3 rat:5,6B'::tsvector, 'A', '{cat,rat}')</code>300        → <code class="returnvalue">'cat':3A 'fat':2,4 'rat':5A,6A</code>301       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">302        <a id="id-1.5.8.19.7.2.2.11.1.1.1" class="indexterm"></a>303        <code class="function">strip</code> ( <code class="type">tsvector</code> )304        → <code class="returnvalue">tsvector</code>305       </p>306       <p>307        Removes positions and weights from the <code class="type">tsvector</code>.308       </p>309       <p>310        <code class="literal">strip('fat:2,4 cat:3 rat:5A'::tsvector)</code>311        → <code class="returnvalue">'cat' 'fat' 'rat'</code>312       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">313        <a id="id-1.5.8.19.7.2.2.12.1.1.1" class="indexterm"></a>314        <code class="function">to_tsquery</code> (315        [<span class="optional"> <em class="parameter"><code>config</code></em> <code class="type">regconfig</code>, </span>]316        <em class="parameter"><code>query</code></em> <code class="type">text</code> )317        → <code class="returnvalue">tsquery</code>318       </p>319       <p>320        Converts text to a <code class="type">tsquery</code>, normalizing words according to321        the specified or default configuration.  The words must be combined322        by valid <code class="type">tsquery</code> operators.323       </p>324       <p>325        <code class="literal">to_tsquery('english', 'The &amp; Fat &amp; Rats')</code>326        → <code class="returnvalue">'fat' &amp; 'rat'</code>327       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">328        <a id="id-1.5.8.19.7.2.2.13.1.1.1" class="indexterm"></a>329        <code class="function">to_tsvector</code> (330        [<span class="optional"> <em class="parameter"><code>config</code></em> <code class="type">regconfig</code>, </span>]331         <em class="parameter"><code>document</code></em> <code class="type">text</code> )332        → <code class="returnvalue">tsvector</code>333       </p>334       <p>335        Converts text to a <code class="type">tsvector</code>, normalizing words according336        to the specified or default configuration.  Position information is337        included in the result.338       </p>339       <p>340        <code class="literal">to_tsvector('english', 'The Fat Rats')</code>341        → <code class="returnvalue">'fat':2 'rat':3</code>342       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">343        <code class="function">to_tsvector</code> (344        [<span class="optional"> <em class="parameter"><code>config</code></em> <code class="type">regconfig</code>, </span>]345        <em class="parameter"><code>document</code></em> <code class="type">json</code> )346        → <code class="returnvalue">tsvector</code>347       </p>348       <p class="func_signature">349        <code class="function">to_tsvector</code> (350        [<span class="optional"> <em class="parameter"><code>config</code></em> <code class="type">regconfig</code>, </span>]351        <em class="parameter"><code>document</code></em> <code class="type">jsonb</code> )352        → <code class="returnvalue">tsvector</code>353       </p>354       <p>355        Converts each string value in the JSON document to356        a <code class="type">tsvector</code>, normalizing words according to the specified357        or default configuration.  The results are then concatenated in358        document order to produce the output.  Position information is359        generated as though one stopword exists between each pair of string360        values.  (Beware that <span class="quote">“<span class="quote">document order</span>”</span> of the fields of a361        JSON object is implementation-dependent when the input362        is <code class="type">jsonb</code>; observe the difference in the examples.)363       </p>364       <p>365        <code class="literal">to_tsvector('english', '{"aa": "The Fat Rats", "b": "dog"}'::json)</code>366        → <code class="returnvalue">'dog':5 'fat':2 'rat':3</code>367       </p>368       <p>369        <code class="literal">to_tsvector('english', '{"aa": "The Fat Rats", "b": "dog"}'::jsonb)</code>370        → <code class="returnvalue">'dog':1 'fat':4 'rat':5</code>371       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">372        <a id="id-1.5.8.19.7.2.2.15.1.1.1" class="indexterm"></a>373        <code class="function">json_to_tsvector</code> (374        [<span class="optional"> <em class="parameter"><code>config</code></em> <code class="type">regconfig</code>, </span>]375        <em class="parameter"><code>document</code></em> <code class="type">json</code>,376        <em class="parameter"><code>filter</code></em> <code class="type">jsonb</code> )377        → <code class="returnvalue">tsvector</code>378       </p>379       <p class="func_signature">380        <a id="id-1.5.8.19.7.2.2.15.1.2.1" class="indexterm"></a>381        <code class="function">jsonb_to_tsvector</code> (382        [<span class="optional"> <em class="parameter"><code>config</code></em> <code class="type">regconfig</code>, </span>]383        <em class="parameter"><code>document</code></em> <code class="type">jsonb</code>,384        <em class="parameter"><code>filter</code></em> <code class="type">jsonb</code> )385        → <code class="returnvalue">tsvector</code>386       </p>387       <p>388        Selects each item in the JSON document that is requested by389        the <em class="parameter"><code>filter</code></em> and converts each one to390        a <code class="type">tsvector</code>, normalizing words according to the specified391        or default configuration.  The results are then concatenated in392        document order to produce the output.  Position information is393        generated as though one stopword exists between each pair of selected394        items.  (Beware that <span class="quote">“<span class="quote">document order</span>”</span> of the fields of a395        JSON object is implementation-dependent when the input396        is <code class="type">jsonb</code>.)397        The <em class="parameter"><code>filter</code></em> must be a <code class="type">jsonb</code>398        array containing zero or more of these keywords:399        <code class="literal">"string"</code> (to include all string values),400        <code class="literal">"numeric"</code> (to include all numeric values),401        <code class="literal">"boolean"</code> (to include all boolean values),402        <code class="literal">"key"</code> (to include all keys), or403        <code class="literal">"all"</code> (to include all the above).404        As a special case, the <em class="parameter"><code>filter</code></em> can also be a405        simple JSON value that is one of these keywords.406       </p>407       <p>408        <code class="literal">json_to_tsvector('english', '{"a": "The Fat Rats", "b": 123}'::json, '["string", "numeric"]')</code>409        → <code class="returnvalue">'123':5 'fat':2 'rat':3</code>410       </p>411       <p>412        <code class="literal">json_to_tsvector('english', '{"cat": "The Fat Rats", "dog": 123}'::json, '"all"')</code>413        → <code class="returnvalue">'123':9 'cat':1 'dog':7 'fat':4 'rat':5</code>414       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">415        <a id="id-1.5.8.19.7.2.2.16.1.1.1" class="indexterm"></a>416        <code class="function">ts_delete</code> ( <em class="parameter"><code>vector</code></em> <code class="type">tsvector</code>, <em class="parameter"><code>lexeme</code></em> <code class="type">text</code> )417        → <code class="returnvalue">tsvector</code>418       </p>419       <p>420        Removes any occurrence of the given <em class="parameter"><code>lexeme</code></em>421        from the <em class="parameter"><code>vector</code></em>.422        The <em class="parameter"><code>lexeme</code></em> string is treated as a lexeme as-is,423        without further processing.424       </p>425       <p>426        <code class="literal">ts_delete('fat:2,4 cat:3 rat:5A'::tsvector, 'fat')</code>427        → <code class="returnvalue">'cat':3 'rat':5A</code>428       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">429        <code class="function">ts_delete</code> ( <em class="parameter"><code>vector</code></em> <code class="type">tsvector</code>, <em class="parameter"><code>lexemes</code></em> <code class="type">text[]</code> )430        → <code class="returnvalue">tsvector</code>431       </p>432       <p>433        Removes any occurrences of the lexemes434        in <em class="parameter"><code>lexemes</code></em>435        from the <em class="parameter"><code>vector</code></em>.436        The strings in <em class="parameter"><code>lexemes</code></em> are taken as lexemes437        as-is, without further processing.  Strings that do not match any438        lexeme in <em class="parameter"><code>vector</code></em> are ignored.439       </p>440       <p>441        <code class="literal">ts_delete('fat:2,4 cat:3 rat:5A'::tsvector, ARRAY['fat','rat'])</code>442        → <code class="returnvalue">'cat':3</code>443       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">444        <a id="id-1.5.8.19.7.2.2.18.1.1.1" class="indexterm"></a>445        <code class="function">ts_filter</code> ( <em class="parameter"><code>vector</code></em> <code class="type">tsvector</code>, <em class="parameter"><code>weights</code></em> <code class="type">"char"[]</code> )446        → <code class="returnvalue">tsvector</code>447       </p>448       <p>449        Selects only elements with the given <em class="parameter"><code>weights</code></em>450        from the <em class="parameter"><code>vector</code></em>.451       </p>452       <p>453        <code class="literal">ts_filter('fat:2,4 cat:3b,7c rat:5A'::tsvector, '{a,b}')</code>454        → <code class="returnvalue">'cat':3B 'rat':5A</code>455       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">456        <a id="id-1.5.8.19.7.2.2.19.1.1.1" class="indexterm"></a>457        <code class="function">ts_headline</code> (458        [<span class="optional"> <em class="parameter"><code>config</code></em> <code class="type">regconfig</code>, </span>]459        <em class="parameter"><code>document</code></em> <code class="type">text</code>,460        <em class="parameter"><code>query</code></em> <code class="type">tsquery</code>461        [<span class="optional">, <em class="parameter"><code>options</code></em> <code class="type">text</code> </span>] )462        → <code class="returnvalue">text</code>463       </p>464       <p>465        Displays, in an abbreviated form, the match(es) for466        the <em class="parameter"><code>query</code></em> in467        the <em class="parameter"><code>document</code></em>, which must be raw text not468        a <code class="type">tsvector</code>.  Words in the document are normalized469        according to the specified or default configuration before matching to470        the query.  Use of this function is discussed in471        <a class="xref" href="textsearch-controls.html#TEXTSEARCH-HEADLINE" title="12.3.4. Highlighting Results">Section 12.3.4</a>, which also describes the472        available <em class="parameter"><code>options</code></em>.473       </p>474       <p>475        <code class="literal">ts_headline('The fat cat ate the rat.', 'cat')</code>476        → <code class="returnvalue">The fat &lt;b&gt;cat&lt;/b&gt; ate the rat.</code>477       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">478        <code class="function">ts_headline</code> (479        [<span class="optional"> <em class="parameter"><code>config</code></em> <code class="type">regconfig</code>, </span>]480        <em class="parameter"><code>document</code></em> <code class="type">json</code>,481        <em class="parameter"><code>query</code></em> <code class="type">tsquery</code>482        [<span class="optional">, <em class="parameter"><code>options</code></em> <code class="type">text</code> </span>] )483        → <code class="returnvalue">text</code>484       </p>485       <p class="func_signature">486        <code class="function">ts_headline</code> (487        [<span class="optional"> <em class="parameter"><code>config</code></em> <code class="type">regconfig</code>, </span>]488        <em class="parameter"><code>document</code></em> <code class="type">jsonb</code>,489        <em class="parameter"><code>query</code></em> <code class="type">tsquery</code>490        [<span class="optional">, <em class="parameter"><code>options</code></em> <code class="type">text</code> </span>] )491        → <code class="returnvalue">text</code>492       </p>493       <p>494        Displays, in an abbreviated form, match(es) for495        the <em class="parameter"><code>query</code></em> that occur in string values496        within the JSON <em class="parameter"><code>document</code></em>.497        See <a class="xref" href="textsearch-controls.html#TEXTSEARCH-HEADLINE" title="12.3.4. Highlighting Results">Section 12.3.4</a> for more details.498       </p>499       <p>500        <code class="literal">ts_headline('{"cat":"raining cats and dogs"}'::jsonb, 'cat')</code>501        → <code class="returnvalue">{"cat": "raining &lt;b&gt;cats&lt;/b&gt; and dogs"}</code>502       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">503        <a id="id-1.5.8.19.7.2.2.21.1.1.1" class="indexterm"></a>504        <code class="function">ts_rank</code> (505        [<span class="optional"> <em class="parameter"><code>weights</code></em> <code class="type">real[]</code>, </span>]506        <em class="parameter"><code>vector</code></em> <code class="type">tsvector</code>,507        <em class="parameter"><code>query</code></em> <code class="type">tsquery</code>508        [<span class="optional">, <em class="parameter"><code>normalization</code></em> <code class="type">integer</code> </span>] )509        → <code class="returnvalue">real</code>510       </p>511       <p>512        Computes a score showing how well513        the <em class="parameter"><code>vector</code></em> matches514        the <em class="parameter"><code>query</code></em>.  See515        <a class="xref" href="textsearch-controls.html#TEXTSEARCH-RANKING" title="12.3.3. Ranking Search Results">Section 12.3.3</a> for details.516       </p>517       <p>518        <code class="literal">ts_rank(to_tsvector('raining cats and dogs'), 'cat')</code>519        → <code class="returnvalue">0.06079271</code>520       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">521        <a id="id-1.5.8.19.7.2.2.22.1.1.1" class="indexterm"></a>522        <code class="function">ts_rank_cd</code> (523        [<span class="optional"> <em class="parameter"><code>weights</code></em> <code class="type">real[]</code>, </span>]524        <em class="parameter"><code>vector</code></em> <code class="type">tsvector</code>,525        <em class="parameter"><code>query</code></em> <code class="type">tsquery</code>526        [<span class="optional">, <em class="parameter"><code>normalization</code></em> <code class="type">integer</code> </span>] )527        → <code class="returnvalue">real</code>528       </p>529       <p>530        Computes a score showing how well531        the <em class="parameter"><code>vector</code></em> matches532        the <em class="parameter"><code>query</code></em>, using a cover density533        algorithm.  See <a class="xref" href="textsearch-controls.html#TEXTSEARCH-RANKING" title="12.3.3. Ranking Search Results">Section 12.3.3</a> for details.534       </p>535       <p>536        <code class="literal">ts_rank_cd(to_tsvector('raining cats and dogs'), 'cat')</code>537        → <code class="returnvalue">0.1</code>538       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">539        <a id="id-1.5.8.19.7.2.2.23.1.1.1" class="indexterm"></a>540        <code class="function">ts_rewrite</code> ( <em class="parameter"><code>query</code></em> <code class="type">tsquery</code>,541        <em class="parameter"><code>target</code></em> <code class="type">tsquery</code>,542        <em class="parameter"><code>substitute</code></em> <code class="type">tsquery</code> )543        → <code class="returnvalue">tsquery</code>544       </p>545       <p>546        Replaces occurrences of <em class="parameter"><code>target</code></em>547        with <em class="parameter"><code>substitute</code></em>548        within the <em class="parameter"><code>query</code></em>.549        See <a class="xref" href="textsearch-features.html#TEXTSEARCH-QUERY-REWRITING" title="12.4.2.1. Query Rewriting">Section 12.4.2.1</a> for details.550       </p>551       <p>552        <code class="literal">ts_rewrite('a &amp; b'::tsquery, 'a'::tsquery, 'foo|bar'::tsquery)</code>553        → <code class="returnvalue">'b' &amp; ( 'foo' | 'bar' )</code>554       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">555        <code class="function">ts_rewrite</code> ( <em class="parameter"><code>query</code></em> <code class="type">tsquery</code>,556        <em class="parameter"><code>select</code></em> <code class="type">text</code> )557        → <code class="returnvalue">tsquery</code>558       </p>559       <p>560        Replaces portions of the <em class="parameter"><code>query</code></em> according to561        target(s) and substitute(s) obtained by executing562        a <code class="command">SELECT</code> command.563        See <a class="xref" href="textsearch-features.html#TEXTSEARCH-QUERY-REWRITING" title="12.4.2.1. Query Rewriting">Section 12.4.2.1</a> for details.564       </p>565       <p>566        <code class="literal">SELECT ts_rewrite('a &amp; b'::tsquery, 'SELECT t,s FROM aliases')</code>567        → <code class="returnvalue">'b' &amp; ( 'foo' | 'bar' )</code>568       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">569        <a id="id-1.5.8.19.7.2.2.25.1.1.1" class="indexterm"></a>570        <code class="function">tsquery_phrase</code> ( <em class="parameter"><code>query1</code></em> <code class="type">tsquery</code>, <em class="parameter"><code>query2</code></em> <code class="type">tsquery</code> )571        → <code class="returnvalue">tsquery</code>572       </p>573       <p>574        Constructs a phrase query that searches575        for matches of <em class="parameter"><code>query1</code></em>576        and <em class="parameter"><code>query2</code></em> at successive lexemes (same577        as <code class="literal">&lt;-&gt;</code> operator).578       </p>579       <p>580        <code class="literal">tsquery_phrase(to_tsquery('fat'), to_tsquery('cat'))</code>581        → <code class="returnvalue">'fat' &lt;-&gt; 'cat'</code>582       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">583        <code class="function">tsquery_phrase</code> ( <em class="parameter"><code>query1</code></em> <code class="type">tsquery</code>, <em class="parameter"><code>query2</code></em> <code class="type">tsquery</code>, <em class="parameter"><code>distance</code></em> <code class="type">integer</code> )584        → <code class="returnvalue">tsquery</code>585       </p>586       <p>587        Constructs a phrase query that searches588        for matches of <em class="parameter"><code>query1</code></em> and589        <em class="parameter"><code>query2</code></em> that occur exactly590        <em class="parameter"><code>distance</code></em> lexemes apart.591       </p>592       <p>593        <code class="literal">tsquery_phrase(to_tsquery('fat'), to_tsquery('cat'), 10)</code>594        → <code class="returnvalue">'fat' &lt;10&gt; 'cat'</code>595       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">596        <a id="id-1.5.8.19.7.2.2.27.1.1.1" class="indexterm"></a>597        <code class="function">tsvector_to_array</code> ( <code class="type">tsvector</code> )598        → <code class="returnvalue">text[]</code>599       </p>600       <p>601        Converts a <code class="type">tsvector</code> to an array of lexemes.602       </p>603       <p>604        <code class="literal">tsvector_to_array('fat:2,4 cat:3 rat:5A'::tsvector)</code>605        → <code class="returnvalue">{cat,fat,rat}</code>606       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">607        <a id="id-1.5.8.19.7.2.2.28.1.1.1" class="indexterm"></a>608        <code class="function">unnest</code> ( <code class="type">tsvector</code> )609        → <code class="returnvalue">setof record</code>610        ( <em class="parameter"><code>lexeme</code></em> <code class="type">text</code>,611        <em class="parameter"><code>positions</code></em> <code class="type">smallint[]</code>,612        <em class="parameter"><code>weights</code></em> <code class="type">text</code> )613       </p>614       <p>615        Expands a <code class="type">tsvector</code> into a set of rows, one per lexeme.616       </p>617       <p>618        <code class="literal">select * from unnest('cat:3 fat:2,4 rat:5A'::tsvector)</code>619        → <code class="returnvalue"></code>620</p><pre class="programlisting">621 lexeme | positions | weights622--------+-----------+---------623 cat    | {3}       | {D}624 fat    | {2,4}     | {D,D}625 rat    | {5}       | {A}626</pre><p>627       </p></td></tr></tbody></table></div></div><br class="table-break" /><div class="note"><h3 class="title">Note</h3><p>628    All the text search functions that accept an optional <code class="type">regconfig</code>629    argument will use the configuration specified by630    <a class="xref" href="runtime-config-client.html#GUC-DEFAULT-TEXT-SEARCH-CONFIG">default_text_search_config</a>631    when that argument is omitted.632   </p></div><p>633   The functions in634   <a class="xref" href="functions-textsearch.html#TEXTSEARCH-FUNCTIONS-DEBUG-TABLE" title="Table 9.44. Text Search Debugging Functions">Table 9.44</a>635   are listed separately because they are not usually used in everyday text636   searching operations.  They are primarily helpful for development and637   debugging of new text search configurations.638  </p><div class="table" id="TEXTSEARCH-FUNCTIONS-DEBUG-TABLE"><p class="title"><strong>Table 9.44. Text Search Debugging Functions</strong></p><div class="table-contents"><table class="table" summary="Text Search Debugging Functions" border="1"><colgroup><col /></colgroup><thead><tr><th class="func_table_entry"><p class="func_signature">639        Function640       </p>641       <p>642        Description643       </p>644       <p>645        Example(s)646       </p></th></tr></thead><tbody><tr><td class="func_table_entry"><p class="func_signature">647        <a id="id-1.5.8.19.10.2.2.1.1.1.1" class="indexterm"></a>648        <code class="function">ts_debug</code> (649        [<span class="optional"> <em class="parameter"><code>config</code></em> <code class="type">regconfig</code>, </span>]650        <em class="parameter"><code>document</code></em> <code class="type">text</code> )651        → <code class="returnvalue">setof record</code>652        ( <em class="parameter"><code>alias</code></em> <code class="type">text</code>,653        <em class="parameter"><code>description</code></em> <code class="type">text</code>,654        <em class="parameter"><code>token</code></em> <code class="type">text</code>,655        <em class="parameter"><code>dictionaries</code></em> <code class="type">regdictionary[]</code>,656        <em class="parameter"><code>dictionary</code></em> <code class="type">regdictionary</code>,657        <em class="parameter"><code>lexemes</code></em> <code class="type">text[]</code> )658       </p>659       <p>660        Extracts and normalizes tokens from661        the <em class="parameter"><code>document</code></em> according to the specified or662        default text search configuration, and returns information about how663        each token was processed.664        See <a class="xref" href="textsearch-debugging.html#TEXTSEARCH-CONFIGURATION-TESTING" title="12.8.1. Configuration Testing">Section 12.8.1</a> for details.665       </p>666       <p>667        <code class="literal">ts_debug('english', 'The Brightest supernovaes')</code>668        → <code class="returnvalue">(asciiword,"Word, all ASCII",The,{english_stem},english_stem,{}) ...</code>669       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">670        <a id="id-1.5.8.19.10.2.2.2.1.1.1" class="indexterm"></a>671        <code class="function">ts_lexize</code> ( <em class="parameter"><code>dict</code></em> <code class="type">regdictionary</code>, <em class="parameter"><code>token</code></em> <code class="type">text</code> )672        → <code class="returnvalue">text[]</code>673       </p>674       <p>675        Returns an array of replacement lexemes if the input token is known to676        the dictionary, or an empty array if the token is known to the677        dictionary but it is a stop word, or NULL if it is not a known word.678        See <a class="xref" href="textsearch-debugging.html#TEXTSEARCH-DICTIONARY-TESTING" title="12.8.3. Dictionary Testing">Section 12.8.3</a> for details.679       </p>680       <p>681        <code class="literal">ts_lexize('english_stem', 'stars')</code>682        → <code class="returnvalue">{star}</code>683       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">684        <a id="id-1.5.8.19.10.2.2.3.1.1.1" class="indexterm"></a>685        <code class="function">ts_parse</code> ( <em class="parameter"><code>parser_name</code></em> <code class="type">text</code>,686        <em class="parameter"><code>document</code></em> <code class="type">text</code> )687        → <code class="returnvalue">setof record</code>688        ( <em class="parameter"><code>tokid</code></em> <code class="type">integer</code>,689        <em class="parameter"><code>token</code></em> <code class="type">text</code> )690       </p>691       <p>692        Extracts tokens from the <em class="parameter"><code>document</code></em> using the693        named parser.694        See <a class="xref" href="textsearch-debugging.html#TEXTSEARCH-PARSER-TESTING" title="12.8.2. Parser Testing">Section 12.8.2</a> for details.695       </p>696       <p>697        <code class="literal">ts_parse('default', 'foo - bar')</code>698        → <code class="returnvalue">(1,foo) ...</code>699       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">700        <code class="function">ts_parse</code> ( <em class="parameter"><code>parser_oid</code></em> <code class="type">oid</code>,701        <em class="parameter"><code>document</code></em> <code class="type">text</code> )702        → <code class="returnvalue">setof record</code>703        ( <em class="parameter"><code>tokid</code></em> <code class="type">integer</code>,704        <em class="parameter"><code>token</code></em> <code class="type">text</code> )705       </p>706       <p>707        Extracts tokens from the <em class="parameter"><code>document</code></em> using a708        parser specified by OID.709        See <a class="xref" href="textsearch-debugging.html#TEXTSEARCH-PARSER-TESTING" title="12.8.2. Parser Testing">Section 12.8.2</a> for details.710       </p>711       <p>712        <code class="literal">ts_parse(3722, 'foo - bar')</code>713        → <code class="returnvalue">(1,foo) ...</code>714       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">715        <a id="id-1.5.8.19.10.2.2.5.1.1.1" class="indexterm"></a>716        <code class="function">ts_token_type</code> ( <em class="parameter"><code>parser_name</code></em> <code class="type">text</code> )717        → <code class="returnvalue">setof record</code>718        ( <em class="parameter"><code>tokid</code></em> <code class="type">integer</code>,719        <em class="parameter"><code>alias</code></em> <code class="type">text</code>,720        <em class="parameter"><code>description</code></em> <code class="type">text</code> )721       </p>722       <p>723        Returns a table that describes each type of token the named parser can724        recognize.725        See <a class="xref" href="textsearch-debugging.html#TEXTSEARCH-PARSER-TESTING" title="12.8.2. Parser Testing">Section 12.8.2</a> for details.726       </p>727       <p>728        <code class="literal">ts_token_type('default')</code>729        → <code class="returnvalue">(1,asciiword,"Word, all ASCII") ...</code>730       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">731        <code class="function">ts_token_type</code> ( <em class="parameter"><code>parser_oid</code></em> <code class="type">oid</code> )732        → <code class="returnvalue">setof record</code>733        ( <em class="parameter"><code>tokid</code></em> <code class="type">integer</code>,734        <em class="parameter"><code>alias</code></em> <code class="type">text</code>,735        <em class="parameter"><code>description</code></em> <code class="type">text</code> )736       </p>737       <p>738        Returns a table that describes each type of token a parser specified739        by OID can recognize.740        See <a class="xref" href="textsearch-debugging.html#TEXTSEARCH-PARSER-TESTING" title="12.8.2. Parser Testing">Section 12.8.2</a> for details.741       </p>742       <p>743        <code class="literal">ts_token_type(3722)</code>744        → <code class="returnvalue">(1,asciiword,"Word, all ASCII") ...</code>745       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">746        <a id="id-1.5.8.19.10.2.2.7.1.1.1" class="indexterm"></a>747        <code class="function">ts_stat</code> ( <em class="parameter"><code>sqlquery</code></em> <code class="type">text</code>748        [<span class="optional">, <em class="parameter"><code>weights</code></em> <code class="type">text</code> </span>] )749        → <code class="returnvalue">setof record</code>750        ( <em class="parameter"><code>word</code></em> <code class="type">text</code>,751        <em class="parameter"><code>ndoc</code></em> <code class="type">integer</code>,752        <em class="parameter"><code>nentry</code></em> <code class="type">integer</code> )753       </p>754       <p>755        Executes the <em class="parameter"><code>sqlquery</code></em>, which must return a756        single <code class="type">tsvector</code> column, and returns statistics about each757        distinct lexeme contained in the data.758        See <a class="xref" href="textsearch-features.html#TEXTSEARCH-STATISTICS" title="12.4.4. Gathering Document Statistics">Section 12.4.4</a> for details.759       </p>760       <p>761        <code class="literal">ts_stat('SELECT vector FROM apod')</code>762        → <code class="returnvalue">(foo,10,15) ...</code>763       </p></td></tr></tbody></table></div></div><br class="table-break" /></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="functions-net.html" title="9.12. Network Address Functions and Operators">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="functions.html" title="Chapter 9. Functions and Operators">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="functions-uuid.html" title="9.14. UUID Functions">Next</a></td></tr><tr><td width="40%" align="left" valign="top">9.12. Network Address Functions and Operators </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"> 9.14. UUID Functions</td></tr></table></div></body></html>
codekingpro/portable-devtools · Team Ai