postgres and unexpected to_tsquery matching

Question

Consider the following:

CREATE TEMPORARY TABLE foo (string text);
INSERT INTO foo VALUES
    ('the small but capable man'),
    ('the small and strong but capable man');
SELECT * FROM foo WHERE to_tsvector(string) @@ to_tsquery('small<->but');
SELECT * FROM foo WHERE to_tsvector(string) @@ to_tsquery('small<->capable');
SELECT * FROM foo WHERE to_tsvector(string) @@ to_tsquery('small<2>capable');

The first query returns both rows, when it should only return one (because small but only appears once as a full phrase). The second query query correctly returns no rows because small and capable are never next to one another. The third correctly returns only one because small and capable are within two of one another.

So question is: why does the first query return both strings? Is there something unique about words like but (or maybe and, etc)?

Wells · Accepted Answer · 2018-05-25 22:16:00Z

1

ah, answer: this is using the english dictionary by default which excludes stop words by default (and, but, etc)-- to really match everything, I use the simple dictionary, e.g. SELECT * FROM foo WHERE to_tsvector('simple',string) @@ to_tsquery('simple','small<->but').

More here.

answered May 25, 2018 at 22:16

Wells

11k16 gold badges58 silver badges93 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

postgres and unexpected to_tsquery matching

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related