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>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 & 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 & 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 & 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">&&</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 && 'cat'::tsquery</code>80 → <code class="returnvalue">( 'fat' | 'rat' ) & '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"><-></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') <-> to_tsquery('rat')</code>113 → <code class="returnvalue">'fat' <-> '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">@></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 @> 'cat & 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"><@</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 <@ 'cat & rat'::tsquery</code>137 → <code class="returnvalue">t</code>138 </p>139 <p>140 <code class="literal">'cat'::tsquery <@ '!cat & 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 & 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' & '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' <-> 'rat'</code>238 </p>239 <p>240 <code class="literal">phraseto_tsquery('english', 'The Cat and Rats')</code>241 → <code class="returnvalue">'cat' <2> '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' <-> 'rat' | 'cat' & '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 & ! 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 & Fat & Rats')</code>326 → <code class="returnvalue">'fat' & '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 <b>cat</b> 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 <b>cats</b> 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 & b'::tsquery, 'a'::tsquery, 'foo|bar'::tsquery)</code>553 → <code class="returnvalue">'b' & ( '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 & b'::tsquery, 'SELECT t,s FROM aliases')</code>567 → <code class="returnvalue">'b' & ( '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"><-></code> operator).578 </p>579 <p>580 <code class="literal">tsquery_phrase(to_tsquery('fat'), to_tsquery('cat'))</code>581 → <code class="returnvalue">'fat' <-> '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' <10> '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>