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
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
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
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
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}) ...
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) ...