How to Use websearch_to_tsquery in PostgreSQL for Text Search

In PostgreSQL, websearch_to_tsquery converts a web-style search string into a tsquery that can be used for full-text search. It supports operators like quotes for phrases, AND, OR, and NOT, making it easier to write search queries similar to web search engines.

Syntax

The basic syntax of websearch_to_tsquery is:

• websearch_to_tsquery([config,] query_text)

Where:

• config (optional) is the text search configuration like 'english'.
• query_text is the search string written in web search style.

The function returns a tsquery value representing the parsed search.

sql

SELECT websearch_to_tsquery('english', 'search phrase AND keyword OR -exclude');

Example

This example shows how to convert a web-style search string into a tsquery and use it to find matching rows in a table with a tsvector column.

sql

CREATE TABLE documents (
  id SERIAL PRIMARY KEY,
  content TEXT,
  content_tsv tsvector
);

INSERT INTO documents (content, content_tsv) VALUES
('PostgreSQL full text search is powerful', to_tsvector('english', 'PostgreSQL full text search is powerful')),
('Learn how to use websearch_to_tsquery function', to_tsvector('english', 'Learn how to use websearch_to_tsquery function')),
('This example excludes certain words', to_tsvector('english', 'This example excludes certain words'));

-- Search for documents matching the web-style query
SELECT id, content
FROM documents
WHERE content_tsv @@ websearch_to_tsquery('english', 'full text AND -exclude');

Output

 id |                      content                      
----+--------------------------------------------------
  1 | PostgreSQL full text search is powerful          
(1 row)

Common Pitfalls

Common mistakes when using websearch_to_tsquery include:

• Not specifying the correct text search configuration, which can affect stemming and stop words.
• Using unsupported operators or syntax that websearch_to_tsquery does not recognize.
• Confusing websearch_to_tsquery with to_tsquery, which uses a different syntax.

Always test your queries to ensure they parse as expected.

sql

/* Wrong: Using to_tsquery syntax with websearch_to_tsquery */
SELECT websearch_to_tsquery('english', 'fat & cat');

/* Right: Use websearch style syntax */
SELECT websearch_to_tsquery('english', 'fat cat');

Output

 websearch_to_tsquery 
----------------------
 'fat' & 'cat'
(1 row)

 websearch_to_tsquery 
----------------------
 'fat' <-> 'cat'
(1 row)

Quick Reference

Summary of websearch_to_tsquery operators:

Operator	Meaning
"phrase"	Exact phrase match
word1 word2	AND between words
word1 OR word2	OR between words
-word	NOT word (exclude)

Operator	Meaning
"phrase"	Exact phrase match
word1 word2	AND between words
word1 OR word2	OR between words
-word	NOT word (exclude)

Key Takeaways

Use websearch_to_tsquery to convert user-friendly search strings into tsquery for full-text search.

Specify the correct text search configuration to get accurate parsing and stemming.

Use web-style operators like quotes, AND, OR, and minus for NOT in your search strings.

Test your queries to avoid syntax confusion between websearch_to_tsquery and to_tsquery.

Combine websearch_to_tsquery with tsvector columns for efficient full-text search filtering.