CODEWITHSUNDEEP
postgresqlfull-text-searchpostgresql-9.1
Migwell
Migwell

Reputation: 20107

Matching only some words in a Postgres full text search

I'm interested in using postgres as the search engine for a website I'm developing, but it seems that postgres is very strict when matching ts_query and ts_vector types. If a ts_vector doesn't contain all items in the query, the match is rejected.

For example I would expect the query 'Stack Overflow code' to match the Stack Overflow site summary, but it doesn't because the word 'code' isn't present, even though 'Stack' and 'Overflow' are.

SELECT 
to_tsvector('Stack Overflow is a question and answer site for professional and enthusiast programmers. It''s built and run by you as part of the Stack Exchange network of Q&A sites. With your help, we''re working together to build a library of detailed answers to every question about programming.')
@@ 
plainto_tsquery('english', 'Stack Overflow code')

Returns:

false

In my use case, I'm not interested in exact matches because this is going to be used by users to search the website.

Is there a way to count something as a match when only part of the query is in the document?

Upvotes: 3

Views: 1471

Answers (1)

fl0cke
fl0cke

Reputation: 2884

That's because plainto_tsquery chops the string into separate lexemes and puts the & (AND) operator between them. That means it matches ALL words.

If you want the | (OR) operator, you need to write your own "parser". For example, you could replace all occurrences of ' ' with '|'

SELECT 
to_tsvector('Stack Overflow is a question and answer site for professional and enthusiast programmers. It''s built and run by you as part of the Stack Exchange network of Q&A sites. With your help, we''re working together to build a library of detailed answers to every question about programming.')
@@ 
to_tsquery('english', replace('Stack Overflow Code', ' ' , '|'));

Upvotes: 5

Related Questions