# 9.13. 文字檢索函式及運算子
[Table 9.40](https://www.postgresql.org/docs/10/static/functions-textsearch.html#textsearch-operators-table),[Table 9.41](https://www.postgresql.org/docs/10/static/functions-textsearch.html#textsearch-functions-table)and[Table 9.42](https://www.postgresql.org/docs/10/static/functions-textsearch.html#textsearch-functions-debug-table)summarize the functions and operators that are provided for full text searching. See[Chapter 12](https://www.postgresql.org/docs/10/static/textsearch.html)for a detailed explanation ofPostgreSQL's text search facility.
**Table 9.40. Text Search Operators**
| Operator | Return Type | Description | Example | Result | | | | | | | |
| -------- | ----------- | ----------------------------- | -------------------------------------------------------------- | --------------------------------- | --------------------- | ----------------- | ------------------------- | --------------------------- | --------- | ------- | ------- |
| `@@` | `boolean` | `tsvector`matches`tsquery`? | `to_tsvector('fat cats ate rats') @@ to_tsquery('cat & rat')` | `t` | | | | | | | |
| `@@@` | `boolean` | deprecated synonym for`@@` | `to_tsvector('fat cats ate rats') @@@ to_tsquery('cat & rat')` | `t` | | | | | | | |
| \` | | \` | `tsvector` | concatenate`tsvector`s | \`'a:1 b:2'::tsvector | | 'c:1 d:2 b:3'::tsvector\` | `'a':1 'b':2,5 'c':3 'd':4` | | | |
| `&&` | `tsquery` | AND`tsquery`s together | \`'fat | rat'::tsquery && 'cat'::tsquery\` | \`( 'fat' | 'rat' ) & 'cat'\` | | | | | |
| \` | | \` | `tsquery` | OR`tsquery`s together | \`'fat | rat'::tsquery | | 'cat'::tsquery\` | \`( 'fat' | 'rat' ) | 'cat'\` |
| `!!` | `tsquery` | negate a`tsquery` | `!! 'cat'::tsquery` | `!'cat'` | | | | | | | |
| `<->` | `tsquery` | `tsquery`followed by`tsquery` | `to_tsquery('fat') <-> to_tsquery('rat')` | `'fat' <-> 'rat'` | | | | | | | |
| `@>` | `boolean` | `tsquery`contains another ? | `'cat'::tsquery @> 'cat & rat'::tsquery` | `f` | | | | | | | |
| `<@` | `boolean` | `tsquery`is contained in ? | `'cat'::tsquery <@ 'cat & rat'::tsquery` | `t` | | | | | | | |
## Note
The`tsquery`containment operators consider only the lexemes listed in the two queries, ignoring the combining operators.
In addition to the operators shown in the table, the ordinary B-tree comparison operators (`=`,`<`, etc) are defined for types`tsvector`and`tsquery`. These are not very useful for text searching but allow, for example, unique indexes to be built on columns of these types.
**Table 9.41. Text Search Functions**
| Function | Return Type | Description | Example | Result | | |
| ----------------------------------------------------------------------------------------- | -------------- | ------------------------------------------------------------------------------------------------------------------------------------ | --------------------------------------------------------------------------------------- | ------------------------------- | --------------- | --------- |
| `array_to_tsvector(text[]`) | `tsvector` | convert array of lexemes to`tsvector` | `array_to_tsvector('{fat,cat,rat}'::text[])` | `'cat' 'fat' 'rat'` | | |
| `get_current_ts_config()` | `regconfig` | get default text search configuration | `get_current_ts_config()` | `english` | | |
| `length(tsvector`) | `integer` | number of lexemes in`tsvector` | `length('fat:2,4 cat:3 rat:5A'::tsvector)` | `3` | | |
| `numnode(tsquery`) | `integer` | number of lexemes plus operators in`tsquery` | \`numnode('(fat & rat) | cat'::tsquery)\` | `5` | |
| `plainto_tsquery([configregconfig`,]`querytext`) | `tsquery` | produce`tsquery`ignoring punctuation | `plainto_tsquery('english', 'The Fat Rats')` | `'fat' & 'rat'` | | |
| `phraseto_tsquery([configregconfig`,]`querytext`) | `tsquery` | produce`tsquery`that searches for a phrase, ignoring punctuation | `phraseto_tsquery('english', 'The Fat Rats')` | `'fat' <-> 'rat'` | | |
| `querytree(querytsquery`) | `text` | get indexable part of a`tsquery` | `querytree('foo & ! bar'::tsquery)` | `'foo'` | | |
| `setweight(vectortsvector`,`weight"char"`) | `tsvector` | assign`weight`*\_to each element of*`vector`\_ | `setweight('fat:2,4 cat:3 rat:5B'::tsvector, 'A')` | `'cat':3A 'fat':2A,4A 'rat':5A` | | |
| `setweight(vectortsvector`,`weight"char"`,`lexemestext[]`) | `tsvector` | assign`weight`*\_to elements of*`vector`*that are listed in*`lexemes`\_ | `setweight('fat:2,4 cat:3 rat:5B'::tsvector, 'A', '{cat,rat}')` | `'cat':3A 'fat':2,4 'rat':5A` | | |
| `strip(tsvector`) | `tsvector` | remove positions and weights from`tsvector` | `strip('fat:2,4 cat:3 rat:5A'::tsvector)` | `'cat' 'fat' 'rat'` | | |
| `to_tsquery([configregconfig`,]`querytext`) | `tsquery` | normalize words and convert to`tsquery` | `to_tsquery('english', 'The & Fat & Rats')` | `'fat' & 'rat'` | | |
| `to_tsvector([configregconfig`,]`documenttext`) | `tsvector` | reduce document text to`tsvector` | `to_tsvector('english', 'The Fat Rats')` | `'fat':2 'rat':3` | | |
| `to_tsvector([configregconfig`,]`documentjson(b)`) | `tsvector` | reduce each string value in the document to a`tsvector`, and then concatentate those in document order to produce a single`tsvector` | `to_tsvector('english', '{"a": "The Fat Rats"}'::json)` | `'fat':2 'rat':3` | | |
| `ts_delete(vectortsvector`,`lexemetext`) | `tsvector` | remove given`lexeme`*\_from*`vector`\_ | `ts_delete('fat:2,4 cat:3 rat:5A'::tsvector, 'fat')` | `'cat':3 'rat':5A` | | |
| `ts_delete(vectortsvector`,`lexemestext[]`) | `tsvector` | remove any occurrence of lexemes in`lexemes`*\_from*`vector`\_ | `ts_delete('fat:2,4 cat:3 rat:5A'::tsvector, ARRAY['fat','rat'])` | `'cat':3` | | |
| `ts_filter(vectortsvector`,`weights"char"[]`) | `tsvector` | select only elements with given`weights`*\_from*`vector`\_ | `ts_filter('fat:2,4 cat:3b rat:5A'::tsvector, '{a,b}')` | `'cat':3B 'rat':5A` | | |
| `ts_headline([configregconfig`,]`documenttext`,`querytsquery`\[,`optionstext`]) | `text` | display a query match | `ts_headline('x y z', 'z'::tsquery)` | `x y z` | | |
| `ts_headline([configregconfig`,]`documentjson(b)`,`querytsquery`\[,`optionstext`]) | `text` | display a query match | `ts_headline('{"a":"x y z"}'::json, 'z'::tsquery)` | `{"a":"x y z"}` | | |
| `ts_rank([weightsfloat4[]`,]`vectortsvector`,`querytsquery`\[,`normalizationinteger`]) | `float4` | rank document for query | `ts_rank(textsearch, query)` | `0.818` | | |
| `ts_rank_cd([weightsfloat4[]`,]`vectortsvector`,`querytsquery`\[,`normalizationinteger`]) | `float4` | rank document for query using cover density | `ts_rank_cd('{0.1, 0.2, 0.4, 1.0}', textsearch, query)` | `2.01317` | | |
| `ts_rewrite(querytsquery`,`targettsquery`,`substitutetsquery`) | `tsquery` | replace`target`*\_with*`substitute`\_within query | \`ts\_rewrite('a & b'::tsquery, 'a'::tsquery, 'foo | bar'::tsquery)\` | \`'b' & ( 'foo' | 'bar' )\` |
| `ts_rewrite(querytsquery`,`selecttext`) | `tsquery` | replace using targets and substitutes from a`SELECT`command | `SELECT ts_rewrite('a & b'::tsquery, 'SELECT t,s FROM aliases')` | \`'b' & ( 'foo' | 'bar' )\` | |
| `tsquery_phrase(query1tsquery`,`query2tsquery`) | `tsquery` | make query that searches for`query1`*\_followed by*`query2`\_(same as`<->`operator) | `tsquery_phrase(to_tsquery('fat'), to_tsquery('cat'))` | `'fat' <-> 'cat'` | | |
| `tsquery_phrase(query1tsquery`,`query2tsquery`,`distanceinteger`) | `tsquery` | make query that searches for`query1`*\_followed by*`query2`*at distance*`distance`\_ | `tsquery_phrase(to_tsquery('fat'), to_tsquery('cat'), 10)` | `'fat' <10> 'cat'` | | |
| `tsvector_to_array(tsvector`) | `text[]` | convert`tsvector`to array of lexemes | `tsvector_to_array('fat:2,4 cat:3 rat:5A'::tsvector)` | `{cat,fat,rat}` | | |
| `tsvector_update_trigger()` | `trigger` | trigger function for automatic`tsvector`column update | `CREATE TRIGGER ... tsvector_update_trigger(tsvcol, 'pg_catalog.swedish', title, body)` | | | |
| `tsvector_update_trigger_column()` | `trigger` | trigger function for automatic`tsvector`column update | `CREATE TRIGGER ... tsvector_update_trigger_column(tsvcol, configcol, title, body)` | | | |
| `unnest(tsvector`, OUT`lexemetext`, OUT`positionssmallint[]`, OUT`weightstext`) | `setof record` | expand a tsvector to a set of rows | `unnest('fat:2,4 cat:3 rat:5A'::tsvector)` | `(cat,{3},{D}) ...` | | |
## Note
All the text search functions that accept an optional`regconfig`argument will use the configuration specified by[default\_text\_search\_config](https://www.postgresql.org/docs/10/static/runtime-config-client.html#guc-default-text-search-config)when that argument is omitted.
The functions in[Table 9.42](https://www.postgresql.org/docs/10/static/functions-textsearch.html#textsearch-functions-debug-table)are listed separately because they are not usually used in everyday text searching operations. They are helpful for development and debugging of new text search configurations.
**Table 9.42. Text Search Debugging Functions**
| Function | Return Type | Description | Example | Result |
| -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | -------------- | ----------------------------------- | -------------------------------------------------- | ---------------------------------------------------------------------- |
| `ts_debug([configregconfig`,]`documenttext`, OUT`aliastext`, OUT`descriptiontext`, OUT`tokentext`, OUT`dictionariesregdictionary[]`, OUT`dictionaryregdictionary`, OUT`lexemestext[]`) | `setof record` | test a configuration | `ts_debug('english', 'The Brightest supernovaes')` | `(asciiword,"Word, all ASCII",The,{english_stem},english_stem,{}) ...` |
| `ts_lexize(dictregdictionary`,`tokentext`) | `text[]` | test a dictionary | `ts_lexize('english_stem', 'stars')` | `{star}` |
| `ts_parse(parser_nametext`,`documenttext`, OUT`tokidinteger`, OUT`tokentext`) | `setof record` | test a parser | `ts_parse('default', 'foo - bar')` | `(1,foo) ...` |
| `ts_parse(parser_oidoid`,`documenttext`, OUT`tokidinteger`, OUT`tokentext`) | `setof record` | test a parser | `ts_parse(3722, 'foo - bar')` | `(1,foo) ...` |
| `ts_token_type(parser_nametext`, OUT`tokidinteger`, OUT`aliastext`, OUT`descriptiontext`) | `setof record` | get token types defined by parser | `ts_token_type('default')` | `(1,asciiword,"Word, all ASCII") ...` |
| `ts_token_type(parser_oidoid`, OUT`tokidinteger`, OUT`aliastext`, OUT`descriptiontext`) | `setof record` | get token types defined by parser | `ts_token_type(3722)` | `(1,asciiword,"Word, all ASCII") ...` |
| `ts_stat(sqlquerytext`, \[`weightstext`,] OUT`wordtext`, OUT`ndocinteger`, OUT`nentryinteger`) | `setof record` | get statistics of a`tsvector`column | `ts_stat('SELECT vector from apod')` | `(foo,10,15) ...` |
---
# Agent Instructions: Querying This Documentation
If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.
Perform an HTTP GET request on the current page URL with the `ask` query parameter:
```
GET https://docs.postgresql.tw/11/the-sql-language/functions-and-operators/9.13.-wen-zi-jian-suo-han-shi-ji-yun-suan-zi.md?ask=
```
The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.
Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.