The PostgreSQL 9.0 Reference Manual - Volume 1A

The PostgreSQL 9.0 Reference Manual - Volume 1A - SQL Language Reference
by The PostgreSQL Global Development Group
Paperback (6"x9"), 454 pages
ISBN 9781906966041
RRP £14.95 ($19.95)

Sales of this book support the PostgreSQL project! Get a printed copy>>>

PostgreSQL 9.0 Reference Manual
Volume 1A

Buy the book here! >>>
support free documentation

7.13 Text Search Functions and Operators

Table 7-36, Table 7-37 and Table 7-38 summarize the functions and operators that are provided for full text searching. See section 10 Full Text Search for a detailed explanation of PostgreSQL's text search facility.

Table 7-36: Text Search Operators

Operator	Description
`@@`	`tsvector` matches `tsquery` ? e.g. `to_tsvector('fat cats ate rats') @@ to_tsquery('cat & rat')` => `t`
`@@@`	deprecated synonym for `@@` e.g. `to_tsvector('fat cats ate rats') @@@ to_tsquery('cat & rat')` => `t`
`\|\|`	concatenate `tsvector`s e.g. `'a:1 b:2'::tsvector \|\| 'c:1 d:2 b:3'::tsvector` => `'a':1 'b':2,5 'c':3 'd':4`
`&&`	AND `tsquery`s together e.g. `'fat \| rat'::tsquery && 'cat'::tsquery` => `( 'fat' \| 'rat' ) & 'cat'`
`\|\|`	OR `tsquery`s together e.g. `'fat \| rat'::tsquery \|\| 'cat'::tsquery` => `( 'fat' \| 'rat' ) \| 'cat'`
`!!`	negate a `tsquery` e.g. `!! 'cat'::tsquery` => `!'cat'`
`@>`	`tsquery` contains another ? e.g. `'cat'::tsquery @> 'cat & rat'::tsquery` => `f`
`<@`	`tsquery` is contained in ? e.g. `'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 7-37: Text Search Functions

Function	Return Type	Description
`to_tsvector([ config regconfig , ] document text)`	`tsvector`	reduce document text to `tsvector` e.g. `to_tsvector('english', 'The Fat Rats')` => `'fat':2 'rat':3`
`length(tsvector)`	`integer`	number of lexemes in `tsvector` e.g. `length('fat:2,4 cat:3 rat:5A'::tsvector)` => `3`
`setweight(tsvector, "char")`	`tsvector`	assign weight to each element of `tsvector` e.g. `setweight('fat:2,4 cat:3 rat:5B'::tsvector, 'A')` => `'cat':3A 'fat':2A,4A 'rat':5A`
`strip(tsvector)`	`tsvector`	remove positions and weights from `tsvector` e.g. `strip('fat:2,4 cat:3 rat:5A'::tsvector)` => `'cat' 'fat' 'rat'`
`to_tsquery([ config regconfig , ] query text)`	`tsquery`	normalize words and convert to `tsquery` e.g. `to_tsquery('english', 'The & Fat & Rats')` => `'fat' & 'rat'`
`plainto_tsquery([ config regconfig , ] query text)`	`tsquery`	produce `tsquery` ignoring punctuation e.g. `plainto_tsquery('english', 'The Fat Rats')` => `'fat' & 'rat'`
`numnode(tsquery)`	`integer`	number of lexemes plus operators in `tsquery` e.g. `numnode('(fat & rat) \| cat'::tsquery)` => `5`
`querytree(query tsquery)`	`text`	get indexable part of a `tsquery` e.g. `querytree('foo & ! bar'::tsquery)` => `'foo'`
`ts_rank([ weights float4[], ] vector tsvector, query tsquery [, normalization integer ])`	`float4`	rank document for query e.g. `ts_rank(textsearch, query)` => `0.818`
`ts_rank_cd([ weights float4[], ] vector tsvector, query tsquery [, normalization integer ])`	`float4`	rank document for query using cover density e.g. `ts_rank_cd('{0.1, 0.2, 0.4, 1.0}', textsearch, query)` => `2.01317`
`ts_headline([ config regconfig, ] document text, query tsquery [, options text ])`	`text`	display a query match e.g. `ts_headline('x y z', 'z'::tsquery)` => `x y <b>z</b>`
`ts_rewrite(query tsquery, target tsquery, substitute tsquery)`	`tsquery`	replace target with substitute within query e.g. `ts_rewrite('a & b'::tsquery, 'a'::tsquery, 'foo\|bar'::tsquery)` => `'b' & ( 'foo' \| 'bar' )`
`ts_rewrite(query tsquery, select text)`	`tsquery`	replace using targets and substitutes from a `SELECT` command e.g. `SELECT ts_rewrite('a & b'::tsquery, 'SELECT t,s FROM aliases')` => `'b' & ( 'foo' \| 'bar' )`
`get_current_ts_config()`	`regconfig`	get default text search configuration e.g. `get_current_ts_config()` => `english`
`tsvector_update_trigger()`	`trigger`	trigger function for automatic `tsvector` column update e.g. `CREATE TRIGGER ... tsvector_update_trigger(tsvcol, 'pg_catalog.swedish', title, body)` =>
`tsvector_update_trigger_column()`	`trigger`	trigger function for automatic `tsvector` column update e.g. `CREATE 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 Table 7-38 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 7-38: Text Search Debugging Functions

Function	Return Type	Description
`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 record`	test a configuration e.g. `ts_debug('english', 'The Brightest supernovaes')` => `(asciiword,"Word, all ASCII",The,{english_stem},english_stem,{}) ...`
`ts_lexize(dict regdictionary, token text)`	`text[]`	test a dictionary e.g. `ts_lexize('english_stem', 'stars')` => `{star}`
`ts_parse(parser_name text, document text, OUT tokid integer, OUT token text)`	`setof record`	test a parser e.g. `ts_parse('default', 'foo - bar')` => `(1,foo) ...`
`ts_parse(parser_oid oid, document text, OUT tokid integer, OUT token text)`	`setof record`	test a parser e.g. `ts_parse(3722, 'foo - bar')` => `(1,foo) ...`
`ts_token_type(parser_name text, OUT tokid integer, OUT alias text, OUT description text)`	`setof record`	get token types defined by parser e.g. `ts_token_type('default')` => `(1,asciiword,"Word, all ASCII") ...`
`ts_token_type(parser_oid oid, OUT tokid integer, OUT alias text, OUT description text)`	`setof record`	get token types defined by parser e.g. `ts_token_type(3722)` => `(1,asciiword,"Word, all ASCII") ...`
`ts_stat(sqlquery text, [ weights text, ] OUT word text, OUT ndoc integer, OUT nentry integer)`	`setof record`	get statistics of a `tsvector` column e.g. `ts_stat('SELECT vector from apod')` => `(foo,10,15) ...`

The PostgreSQL 9.0 Reference Manual - Volume 1A - SQL Language Reference

See the print edition