Search code examples
postgresqlfull-text-searchtsvector

PostgreSQL Full Text Search and reserved words, preserving some words

I am using Postgresql with full test search with english dict. When I want to receive records with some english words I get verid results.

And so:

SELECT id FROM table1 WHERE ts_vector1 @@ to_tsquery('it')

returns 0 results.

SELECT id FROM table1 WHERE ts_vector1 @@ to_tsquery('specialist & it')

returns more than 0 results (word 'it' exists in table and index). ts_vector1 is created as follow:

ts_vector1 = to_tsvector('english', some_text_column)

Is 'it' a reserved word? If so, what is the best way to 'escape' reserved words?

Solution

  • 'It' is ignored as a stop word, per the relevant docs:

    http://www.postgresql.org/docs/current/static/textsearch-controls.html

    In the example above we see that the resulting tsvector does not contain the words a, on, or it, the word rats became rat, and the punctuation sign - was ignored.

    You can change the list of stop words by configuring the needed dictionaries:

    http://www.postgresql.org/docs/current/static/textsearch-dictionaries.html