PostgreSQL
9.13. Text Search Functions and Operators
Table 9.41, Table 9.42 and Table 9.43 summarize the functions and operators that are provided for full text searching. See Chapter 12 for a detailed explanation of PostgreSQL’s text search facility.
Table 9.41. Text Search Operators
| Operator Description Example(s) |
|---|
Does
|
Does text string, after implicit invocation of
|
This is a deprecated synonym for
|
|
` `+tsvector` → Concatenates two `tsvector`s. If both inputs contain lexeme positions, the second input’s positions are adjusted accordingly. `+'a:1 b:2'::tsvector |
'c:1 d:2 b:3'::tsvector+` → |
ANDs two `tsquery`s together, producing a query that matches documents that match both input queries. `+'fat |
rat'::tsquery && 'cat'::tsquery+` → `+( 'fat' |
'rat' ) & 'cat'+` |
|
` `+tsquery` → ORs two `tsquery`s together, producing a query that matches documents that match either input query. `+'fat |
rat'::tsquery |
'cat'::tsquery+` → `+'fat' |
'rat' |
'cat'+` |
Negates a
|
Constructs a phrase query, which matches if the two input queries match at successive lexemes.
|
Does first
|
Is first
|
+
In addition to these specialized operators, the usual comparison operators shown in Table 9.1 are available 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.42. Text Search Functions
| Function Description Example(s) |
|---|
`+array_to_tsvector+` ( `+text[]+` ) → `+tsvector+` Converts an array of lexemes to a
|
`+get_current_ts_config+` ( ) → `+regconfig+` Returns the OID of the current default text search configuration (as set by default_text_search_config).
|
`+length+` ( `+tsvector+` ) → `+integer+` Returns the number of lexemes in the
|
`+numnode+` ( `+tsquery+` ) → `+integer+` Returns the number of lexemes plus operators in the `+numnode('(fat & rat) |
cat'::tsquery)` → `+5` |
`+plainto_tsquery+` ( [ [.optional]#_`+config+`_ `+regconfig+`,# ] _`+query+`_ `+text+` ) → `+tsquery+` Converts text to a
|
`+phraseto_tsquery+` ( [ [.optional]#_`+config+`_ `+regconfig+`,# ] _`+query+`_ `+text+` ) → `+tsquery+` Converts text to a
|
`+websearch_to_tsquery+` ( [ [.optional]#_`+config+`_ `+regconfig+`,# ] _`+query+`_ `+text+` ) → `+tsquery+` Converts text to a
|
'cat' & 'dog'+` |
`+querytree+` ( `+tsquery+` ) → `+text+` Produces a representation of the indexable portion of a
|
`+setweight+` ( _`+vector+`_ `+tsvector+`, _`+weight+`_ `+"char"+` ) → `+tsvector+` Assigns the specified `weight
|
`+setweight+` ( _`+vector+`_ `+tsvector+`, _`+weight+`_ `+"char"+`, _`+lexemes+`_ `+text[]+` ) → `+tsvector+` Assigns the specified `weight
|
`+strip+` ( `+tsvector+` ) → `+tsvector+` Removes positions and weights from the
|
`+to_tsquery+` ( [ [.optional]#_`+config+`_ `+regconfig+`,# ] _`+query+`_ `+text+` ) → `+tsquery+` Converts text to a
|
`+to_tsvector+` ( [ [.optional]#_`+config+`_ `+regconfig+`,# ] _`+document+`_ `+text+` ) → `+tsvector+` Converts text to a
|
Converts each string value in the JSON document to a
|
`+json_to_tsvector+` ( [ [.optional]#_`+config+`_ `+regconfig+`,# ] _`+document+`_ `+json+`, _`+filter+`_ `+jsonb+` ) → `+tsvector+` `+jsonb_to_tsvector+` ( [ [.optional]#_`+config+`_ `+regconfig+`,# ] _`+document+`_ `+jsonb+`, _`+filter+`_ `+jsonb+` ) → `+tsvector+` Selects each item in the JSON document that is requested by the `filter
|
`+ts_delete+` ( _`+vector+`_ `+tsvector+`, _`+lexeme+`_ `+text+` ) → `+tsvector+` Removes any occurrence of the given `lexeme
|
Removes any occurrences of the lexemes in `lexemes
|
`+ts_filter+` ( _`+vector+`_ `+tsvector+`, _`+weights+`_ `+"char"[]+` ) → `+tsvector+` Selects only elements with the given `weights
|
`+ts_headline+` ( [ [.optional]#_`+config+`_ `+regconfig+`,# ] _`+document+`_ `+text+`, _`+query+`_ `+tsquery+` [[.optional]#, _`+options+`_ `+text+`# ] ) → `+text+` Displays, in an abbreviated form, the match(es) for the `query
|
Displays, in an abbreviated form, match(es) for the `query
|
`+ts_rank+` ( [ [.optional]#_`+weights+`_ `+real[]+`,# ] _`+vector+`_ `+tsvector+`, _`+query+`_ `+tsquery+` [[.optional]#, _`+normalization+`_ `+integer+`# ] ) → `+real+` Computes a score showing how well the `vector
|
`+ts_rank_cd+` ( [ [.optional]#_`+weights+`_ `+real[]+`,# ] _`+vector+`_ `+tsvector+`, _`+query+`_ `+tsquery+` [[.optional]#, _`+normalization+`_ `+integer+`# ] ) → `+real+` Computes a score showing how well the `vector
|
`+ts_rewrite+` ( _`+query+`_ `+tsquery+`, _`+target+`_ `+tsquery+`, _`+substitute+`_ `+tsquery+` ) → `+tsquery+` Replaces occurrences of `target `+ts_rewrite('a & b'::tsquery, 'a'::tsquery, 'foo |
bar'::tsquery)` → `'b' & ( 'foo' |
'bar' )+` |
Replaces portions of the `query
|
'bar' )+` |
`+tsquery_phrase+` ( _`+query1+`_ `+tsquery+`, _`+query2+`_ `+tsquery+` ) → `+tsquery+` Constructs a phrase query that searches for matches of `query1
|
Constructs a phrase query that searches for matches of `query1
|
`+tsvector_to_array+` ( `+tsvector+` ) → `+text[]+` Converts a
|
`+unnest+` ( `+tsvector+` ) → `+setof record+` ( _`+lexeme+`_ `+text+`, _`+positions+`_ `+smallint[]+`, _`+weights+`_ `+text+` ) Expands a
|
positions |
weights ---------------------------- cat |
{3} |
{D} fat |
{2,4} |
{D,D} rat |
{5} |
{A} ---- |
+
Note
All the text search functions that accept an optional regconfig argument will use the configuration specified by default_text_search_config when that argument is omitted.
The functions in Table 9.43 are listed separately because they are not usually used in everyday text searching operations. They are primarily helpful for development and debugging of new text search configurations.
Table 9.43. Text Search Debugging Functions
| Function Description Example(s) |
|---|
`+ts_debug+` ( [ [.optional]#_`+config+`_ `+regconfig+`,# ] _`+document+`_ `+text+` ) → `+setof record+` ( _`+alias+`_ `+text+`, _`+description+`_ `+text+`, _`+token+`_ `+text+`, _`+dictionaries+`_ `+regdictionary[]+`, _`+dictionary+`_ `+regdictionary+`, _`+lexemes+`_ `+text[]+` ) Extracts and normalizes tokens from the `document` according to the specified or default text search configuration, and returns information about how each token was processed. See Section 12.8.1 for details.
|
`+ts_lexize+` ( _`+dict+`_ `+regdictionary+`, _`+token+`_ `+text+` ) → `+text[]+` Returns an array of replacement lexemes if the input token is known to the dictionary, or an empty array if the token is known to the dictionary but it is a stop word, or NULL if it is not a known word. See Section 12.8.3 for details.
|
`+ts_parse+` ( _`+parser_name+`_ `+text+`, _`+document+`_ `+text+` ) → `+setof record+` ( _`+tokid+`_ `+integer+`, _`+token+`_ `+text+` ) Extracts tokens from the `document` using the named parser. See Section 12.8.2 for details.
|
Extracts tokens from the `document` using a parser specified by OID. See Section 12.8.2 for details.
|
`+ts_token_type+` ( _`+parser_name+`_ `+text+` ) → `+setof record+` ( _`+tokid+`_ `+integer+`, _`+alias+`_ `+text+`, _`+description+`_ `+text+` ) Returns a table that describes each type of token the named parser can recognize. See Section 12.8.2 for details.
|
Returns a table that describes each type of token a parser specified by OID can recognize. See Section 12.8.2 for details.
|
`+ts_stat+` ( _`+sqlquery+`_ `+text+` [[.optional]#, _`+weights+`_ `+text+`# ] ) → `+setof record+` ( _`+word+`_ `+text+`, _`+ndoc+`_ `+integer+`, _`+nentry+`_ `+integer+` ) Executes the `sqlquery
|
+
| Prev | Up | Next |
|---|---|---|
9.12. Network Address Functions and Operators |
9.14. UUID Functions |
Submit correction
If you see anything in the documentation that is not correct, does not match your experience with the particular feature or requires further clarification, please use this form to report a documentation issue.
Copyright © 1996-2023 The PostgreSQL Global Development Group