Postgresql – Postgres text search has unexpected query conversion

full-text-searchpostgresql

I have a dictionary built in this way:

DROP TEXT SEARCH DICTIONARY IF EXISTS simple_english_with_stopwords CASCADE;
CREATE TEXT SEARCH DICTIONARY simple_english_with_stopwords (TEMPLATE = pg_catalog.simple);
CREATE TEXT SEARCH CONFIGURATION simple_english_with_stopwords (copy = english);
ALTER TEXT SEARCH CONFIGURATION simple_english_with_stopwords
   ALTER MAPPING FOR asciihword, asciiword, hword, hword_asciipart, hword_part, word
   WITH simple_english_with_stopwords;

which should include not only generic terms but stop words.

Based on that I am expecting that something like:

SELECT to_tsvector('open plan') @@ to_tsquery('off <-> plan');

would return false as off is not in the text. But for some reason it is returning true.

If I run just: 

# SELECT to_tsquery('off <-> plan');
 to_tsquery
------------
 'plan'
(1 row)

which explains why the previous expression is returning true. But why the query has removed the off term? Any idea?

Best Answer

You have created a new dictionary and such, but you are not using it.

SELECT to_tsquery('simple_english_with_stopwords','off <-> plan');
    to_tsquery    
------------------
 'off' <-> 'plan'
(1 row)

Or:

set default_text_search_config TO 'simple_english_with_stopwords';
SELECT to_tsquery('off <-> plan');
    to_tsquery    
------------------
 'off' <-> 'plan'

The first, more verbose, option is better if you want to use FTS indexes, otherwise you run into mutability problems.

Related Question