to_tsquery for search terms in PostgreSQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What does the to_tsquery function do in PostgreSQL?

to_tsquery converts a text search query string into a format that PostgreSQL can use to perform full-text searches.

beginner

How do you combine multiple search terms using to_tsquery?

You use the & operator to combine terms with AND, and the | operator to combine terms with OR inside the to_tsquery string.

intermediate

What is the difference between to_tsquery and plainto_tsquery?

to_tsquery expects a query with operators like & or |, while plainto_tsquery treats the input as plain text and automatically combines terms with AND.

beginner

Write a to_tsquery expression to search for documents containing both 'cat' and 'dog'.

to_tsquery('cat & dog')

intermediate

What happens if you use to_tsquery with an empty string?

It returns an empty query that matches nothing, so no rows will be found in a search.

Which operator in to_tsquery means AND?

A :

B &

C !

D |

The & operator means AND in to_tsquery.

What does to_tsquery('cat | dog') search for?

A Documents containing either 'cat' or 'dog'

B Documents containing neither 'cat' nor 'dog'

C Documents containing both 'cat' and 'dog'

D Documents containing the phrase 'cat dog'

The | operator means OR, so it searches for documents containing either 'cat' or 'dog'.

Which function automatically treats input as plain text and combines terms with AND?

A to_tsquery

B ts_rank

C tsvector

D plainto_tsquery

plainto_tsquery treats input as plain text and combines terms with AND automatically.

How would you write a to_tsquery to search for 'apple' but not 'banana'?

A to_tsquery('apple ! banana')

B to_tsquery('apple | banana')

C to_tsquery('apple & !banana')

D to_tsquery('apple & banana')

The ! operator means NOT, so apple & !banana searches for 'apple' but excludes 'banana'.

What type of data does to_tsquery return?

A tsquery type

B Text string

C Integer

D Boolean

to_tsquery returns a tsquery type used for full-text search matching.

Explain how to use to_tsquery to search for multiple terms with AND and OR operators.

Think about how you combine words inside the query string.

Describe the difference between to_tsquery and plainto_tsquery and when to use each.

One expects operators, the other treats input as simple words.