How to search for partial matches from a GIN index in a multi-word PostgreSQL query?

Question

I have a GIN index in a Postgres database that I'm currently using to do full text search. So if a tsvector field contains the vectorized form of "mary had 4 little lambs" then I could search for "4 little lambs" with:

SELECT * FROM mytable WHERE text_index @@ to_tsquery('pg_catalog.english', '''4 little lambs'':*');

However, if I try to search for "4 lambs" with:

SELECT * FROM mytable WHERE text_index @@ to_tsquery('pg_catalog.english', '''4 lambs'':*');

this query returns no results.

Why isn't the index finding any partial matches? What's wrong with my syntax?

Answer 1

Your second tsquery is

SELECT to_tsquery('english', '''4 lambs'':*');
 to_tsquery     

════════════════════
 '4':* <-> 'lamb':*
(1 row)

That is a phrase where a word starting with "4" is right before a word starting with "lamb". That won't match "4 little lambs".

If you want to search for texts that contain both "4" and "lamb" anywhere in the text, you should use

SELECT to_tsquery('pg_catalog.english', '4 & lambs');
to_tsquery

══════════════
 '4' & 'lamb'
(1 row)