tsvector and tsquery types in PostgreSQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What is the tsvector type used for in PostgreSQL?

tsvector stores pre-processed text for full-text search. It breaks text into lexemes (words) and removes duplicates, making search faster.

beginner

What does the tsquery type represent in PostgreSQL?

tsquery represents a search query for full-text search. It contains lexemes and operators to find matching tsvector entries.

intermediate

How do tsvector and tsquery work together?

tsvector holds searchable text data, and tsquery holds the search pattern. PostgreSQL matches tsquery against tsvector to find relevant rows.

beginner

What PostgreSQL operator is used to match tsquery against tsvector?

The @@ operator is used. For example, tsvector_column @@ tsquery_expression returns true if the query matches the text.

beginner

How can you convert plain text to tsvector in PostgreSQL?

Use the to_tsvector('language', 'text') function. It processes the text into lexemes for searching.

Which PostgreSQL type stores processed searchable text for full-text search?

A varchar

B tsquery

C text

D tsvector

tsvector stores processed text for searching, while tsquery stores the search query.

What operator is used to check if a tsquery matches a tsvector?

A @@

B ~~

C =

D LIKE

The @@ operator matches tsquery against tsvector.

Which function converts plain text into a tsvector?

A text_to_vector()

B to_tsquery()

C to_tsvector()

D vectorize_text()

to_tsvector() converts text to tsvector. to_tsquery() creates a tsquery.

What does tsquery contain?

A Search query with lexemes and operators

B Raw text data

C Database schema

D Table indexes

tsquery holds the search pattern with words and logical operators.

Which of these is true about tsvector?

A It stores unprocessed text

B It stores processed lexemes for searching

C It is used to write queries

D It is a type of index

tsvector stores processed lexemes, not raw text.

Explain how tsvector and tsquery types work together in PostgreSQL full-text search.

Think about how text is prepared and then searched.

Describe the purpose of the to_tsvector and to_tsquery functions.

One prepares text to be searched, the other prepares the search pattern.