How do I perform a text search in a query?

Use the @@ operator with each column you want to search. A simple use of the @@ operator can take the form <column>@@<words> where <column> is the name of the column to be searched and <words> is a list of words to search for. For example,

WHERE mycolumn@@'cat sat mat'

will match rows where mycolumn contains all three words in any order.

For more complex text searches, use the form <column>@@to_tsquery('simple',<expression>) where <expression> is a logical search expression that contain the following operators.

Operator Description
! Negates (NOT) the expression that follows it
& Conjunctive operator (AND) of two expressions
| Disjunctive operator (OR) of two expressions

For example,

WHERE mycolumn@@to_tsquery('simple','!cat & (sat | mat)')

will match rows where mycolumn does not contain cat and but does contain sat or mat.

Note that complex match expressions can use round brackets to denote precedence.  


Have more questions? Submit a request


Article is closed for comments.

Powered by Zendesk