跳到主要内容

Text Search Functions and Operators in Cloudberry Database

The following tables summarize the functions and operators that are provided for full text searching.

OperatorDescriptionExampleResult
@@tsvector matches tsquery ?to_tsvector('fat cats ate rats') @@ to_tsquery('cat & rat')t
@@@deprecated synonym for @@to_tsvector('fat cats ate rats') @@@ to_tsquery('cat & rat')t
||concatenatetsvectors'a:1 b:2'::tsvector || 'c:1 d:2 b:3'::tsvector'a':1 'b':2,5 'c':3 'd':4
&&AND tsquerys together'fat | rat'::tsquery && 'cat'::tsquery( 'fat' | 'rat' ) & 'cat'
||OR tsquerys together'fat | rat'::tsquery || 'cat'::tsquery( 'fat' | 'rat' ) | 'cat'
!!negate atsquery!! 'cat'::tsquery!'cat'
@>tsquery contains another ?'cat'::tsquery @> 'cat & rat'::tsqueryf
<@tsquery is contained in ?'cat'::tsquery <@ 'cat & rat'::tsqueryt

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.

FunctionReturn TypeDescriptionExampleResult
get_current_ts_config()regconfigget default text search configurationget_current_ts_config()english
length(tsvector)integernumber of lexemes in tsvectorlength('fat:2,4 cat:3 rat:5A'::tsvector)3
numnode(tsquery)integernumber of lexemes plus operators in tsquerynumnode('(fat & rat) | cat'::tsquery)5
plainto_tsquery([ config regconfig , ] querytext)tsqueryproduce tsquery ignoring punctuationplainto_tsquery('english', 'The Fat Rats')'fat' & 'rat'
querytree(query tsquery)textget indexable part of a tsqueryquerytree('foo & ! bar'::tsquery)'foo'
setweight(tsvector, "char")tsvectorassign weight to each element of tsvectorsetweight('fat:2,4 cat:3 rat:5B'::tsvector, 'A')'cat':3A 'fat':2A,4A 'rat':5A
strip(tsvector)tsvectorremove positions and weights from tsvectorstrip('fat:2,4 cat:3 rat:5A'::tsvector)'cat' 'fat' 'rat'
to_tsquery([ config regconfig , ] query text)tsquerynormalize words and convert to tsqueryto_tsquery('english', 'The & Fat & Rats')'fat' & 'rat'
to_tsvector([ config regconfig , ] documenttext)tsvectorreduce document text to tsvectorto_tsvector('english', 'The Fat Rats')'fat':2 'rat':3
ts_headline([ config regconfig, ] documenttext, query tsquery [, options text ])textdisplay a query matchts_headline('x y z', 'z'::tsquery)x y z
ts_rank([ weights float4[], ] vector tsvector,query tsquery [, normalization integer ])float4rank document for queryts_rank(textsearch, query)0.818
ts_rank_cd([ weights float4[], ] vectortsvector, query tsquery [, normalizationinteger ])float4rank document for query using cover densityts_rank_cd('1', textsearch, query)2.01317
ts_rewrite(query tsquery, target tsquery,substitute tsquery)tsqueryreplace target with substitute within queryts_rewrite('a & b'::tsquery, 'a'::tsquery, 'foo|bar'::tsquery)'b' & ( 'foo' | 'bar' )
ts_rewrite(query tsquery, select text)tsqueryreplace using targets and substitutes from a SELECTcommandSELECT ts_rewrite('a & b'::tsquery, 'SELECT t,s FROM aliases')'b' & ( 'foo' | 'bar' )
tsvector_update_trigger()triggertrigger function for automatic tsvector column updateCREATE TRIGGER ... tsvector_update_trigger(tsvcol, 'pg_catalog.swedish', title, body) 
tsvector_update_trigger_column()triggertrigger function for automatic tsvector column updateCREATE TRIGGER ... tsvector_update_trigger_column(tsvcol, configcol, title, body) 

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 the following 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.

FunctionReturn TypeDescriptionExampleResult
ts_debug([ *config* regconfig, ] *document* text, OUT *alias* text, OUT *description* text, OUT *token* text, OUT *dictionaries* regdictionary[], OUT *dictionary* regdictionary, OUT *lexemes* text[])setof recordtest a configurationts_debug('english', 'The Brightest supernovaes')(asciiword,"Word, all ASCII",The,{english_stem},english_stem,{}) ...
ts_lexize(*dict* regdictionary, *token* text)text[]test a dictionaryts_lexize('english_stem', 'stars'){star}
ts_parse(*parser_name* text, *document* text, OUT *tokid* integer, OUT *token* text)setof recordtest a parserts_parse('default', 'foo - bar')(1,foo) ...
ts_parse(*parser_oid* oid, *document* text, OUT *tokid* integer, OUT *token* text)setof recordtest a parserts_parse(3722, 'foo - bar')(1,foo) ...
ts_token_type(*parser_name* text, OUT *tokid* integer, OUT *alias* text, OUT description text)setof recordget token types defined by parserts_token_type('default')(1,asciiword,"Word, all ASCII") ...
ts_token_type(*parser_oid* oid, OUT *tokid* integer, OUT *alias* text, OUT *description* text)setof recordget token types defined by parserts_token_type(3722)(1,asciiword,"Word, all ASCII") ...
ts_stat(*sqlquery* text, [ *weights* text, ] OUT *word* text, OUT *ndocinteger*, OUT *nentry* integer)setof recordget statistics of a tsvectorcolumnts_stat('SELECT vector from apod')(foo,10,15) ...