9.13. 文字檢索函式及運算子

Table 9.40,Table 9.41andTable 9.42summarize the functions and operators that are provided for full text searching. SeeChapter 12for a detailed explanation ofPostgreSQL’s text search facility.

Table 9.40. Text Search Operators

Operator Return Type Description Example Result
@@ boolean tsvectormatchestsquery? 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 concatenatetsvectors `‘a:1 b:2’::tsvector ‘c:1 d:2 b:3’::tsvector` 'a':1 'b':2,5 'c':3 'd':4
&& tsquery ANDtsquerys together `‘fat rat’::tsquery && ‘cat’::tsquery` `( ‘fat’ ‘rat’ ) & ‘cat’`
` ` tsquery ORtsquerys together `‘fat rat’::tsquery ‘cat’::tsquery` `( ‘fat’ ‘rat’ ) ‘cat’`
!! tsquery negate atsquery !! 'cat'::tsquery !'cat'
<-> tsquery tsqueryfollowed bytsquery to_tsquery('fat') <-> to_tsquery('rat') 'fat' <-> 'rat'
@> boolean tsquerycontains another ? 'cat'::tsquery @> 'cat & rat'::tsquery f
<@ boolean tsqueryis contained in ? 'cat'::tsquery <@ 'cat & rat'::tsquery t

Note

Thetsquerycontainment 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 typestsvectorandtsquery. 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 totsvector 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 intsvector length('fat:2,4 cat:3 rat:5A'::tsvector) 3
numnode(tsquery) integer number of lexemes plus operators intsquery `numnode(‘(fat & rat) cat’::tsquery)` 5
plainto_tsquery([configregconfig,]querytext) tsquery producetsqueryignoring punctuation plainto_tsquery('english', 'The Fat Rats') 'fat' & 'rat'
phraseto_tsquery([configregconfig,]querytext) tsquery producetsquerythat searches for a phrase, ignoring punctuation phraseto_tsquery('english', 'The Fat Rats') 'fat' <-> 'rat'
querytree(querytsquery) text get indexable part of atsquery querytree('foo & ! bar'::tsquery) 'foo'
setweight(vectortsvector,weight"char") tsvector assignweight_to each element ofvector_ setweight('fat:2,4 cat:3 rat:5B'::tsvector, 'A') 'cat':3A 'fat':2A,4A 'rat':5A
setweight(vectortsvector,weight"char",lexemestext[]) tsvector assignweight_to elements ofvectorthat are listed inlexemes_ 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 fromtsvector strip('fat:2,4 cat:3 rat:5A'::tsvector) 'cat' 'fat' 'rat'
to_tsquery([configregconfig,]querytext) tsquery normalize words and convert totsquery to_tsquery('english', 'The & Fat & Rats') 'fat' & 'rat'
to_tsvector([configregconfig,]documenttext) tsvector reduce document text totsvector to_tsvector('english', 'The Fat Rats') 'fat':2 'rat':3
to_tsvector([configregconfig,]documentjson(b)) tsvector reduce each string value in the document to atsvector, and then concatentate those in document order to produce a singletsvector to_tsvector('english', '{"a": "The Fat Rats"}'::json) 'fat':2 'rat':3
ts_delete(vectortsvector,lexemetext) tsvector remove givenlexeme_fromvector_ 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 inlexemes_fromvector_ 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 givenweights_fromvector_ 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 <b>z</b>
ts_headline([configregconfig,]documentjson(b),querytsquery[,optionstext]) text display a query match ts_headline('{"a":"x y z"}'::json, 'z'::tsquery) {"a":"x y <b>z</b>"}
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 replacetarget_withsubstitute_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 aSELECTcommand SELECT ts_rewrite('a & b'::tsquery, 'SELECT t,s FROM aliases') `‘b’ & ( ‘foo’ ‘bar’ )`
tsquery_phrase(query1tsquery,query2tsquery) tsquery make query that searches forquery1_followed byquery2_(same as<->operator) tsquery_phrase(to_tsquery('fat'), to_tsquery('cat')) 'fat' <-> 'cat'
tsquery_phrase(query1tsquery,query2tsquery,distanceinteger) tsquery make query that searches forquery1_followed byquery2at distancedistance_ tsquery_phrase(to_tsquery('fat'), to_tsquery('cat'), 10) 'fat' <10> 'cat'
tsvector_to_array(tsvector) text[] converttsvectorto array of lexemes tsvector_to_array('fat:2,4 cat:3 rat:5A'::tsvector) {cat,fat,rat}
tsvector_update_trigger() trigger trigger function for automatictsvectorcolumn update CREATE TRIGGER ... tsvector_update_trigger(tsvcol, 'pg_catalog.swedish', title, body)
tsvector_update_trigger_column() trigger trigger function for automatictsvectorcolumn update CREATE TRIGGER ... tsvector_update_trigger_column(tsvcol, configcol, title, body)
unnest(tsvector, OUTlexemetext, OUTpositionssmallint[], OUTweightstext) 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 optionalregconfigargument will use the configuration specified bydefault_text_search_configwhen that argument is omitted.

The functions inTable 9.42are 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, OUTaliastext, OUTdescriptiontext, OUTtokentext, OUTdictionariesregdictionary[], OUTdictionaryregdictionary, OUTlexemestext[]) 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, OUTtokidinteger, OUTtokentext) setof record test a parser ts_parse('default', 'foo - bar') (1,foo) ...
ts_parse(parser_oidoid,documenttext, OUTtokidinteger, OUTtokentext) setof record test a parser ts_parse(3722, 'foo - bar') (1,foo) ...
ts_token_type(parser_nametext, OUTtokidinteger, OUTaliastext, OUTdescriptiontext) setof record get token types defined by parser ts_token_type('default') (1,asciiword,"Word, all ASCII") ...
ts_token_type(parser_oidoid, OUTtokidinteger, OUTaliastext, OUTdescriptiontext) setof record get token types defined by parser ts_token_type(3722) (1,asciiword,"Word, all ASCII") ...
ts_stat(sqlquerytext, [weightstext,] OUTwordtext, OUTndocinteger, OUTnentryinteger) setof record get statistics of atsvectorcolumn ts_stat('SELECT vector from apod') (foo,10,15) ...