PostgreSQL: websearch_to_tsquery precedence / predicate ordering

full-text-searchpostgresql

How does precedence work in websearch_to_tsquery? I have below example query:

websearch_to_tsquery('exp1 exp2 OR "multi word expression" -weak')

The issue I have is that the results include text containing 'weak' because they match 'exp1 exp2'.

However when I remove OR "multi word expression eg query being

websearch_to_tsquery('exp1 exp2 -weak')

then it works as expected and weak is not present in results.

I suspect the initial query is interpreted as

(exp1 and exp2) OR ("multi word expression" and not weak)

instead of my intention of
(exp1 and exp2) OR ("multi word expression") and not weak

So how would I write the later query using `websearch_to_tsquery?

Best Answer

This is how PostgreSQL interprets your query:

SELECT websearch_to_tsquery('english', 'exp1 exp2 OR "multi word expression" -weak');

                     websearch_to_tsquery                     
--------------------------------------------------------------
 'exp1' & 'exp2' | 'multi' <-> 'word' <-> 'express' & !'weak'
(1 row)

Since & ties stronger than |, your suspicion is correct.

I don't think that there is a simpler way to express what you want with websearch_to_tsquery than

exp1 exp2 -weak OR "multi word expression" -weak

Related Solutions

Postgresql – What does “GIN doesn’t support full index scans” mean

a word or two belonging to a pre-set, almost 'constant', list of words.

Have you considered scanning the texts as they are input and flagging them (possibly with a many-to-many link to another table containing your list of keywords)?

Oracle “matches” multiple columns

One method is to use the MULTI_COLUMN_DATASTORE. Below is an example:

drop table t1 purge;

create table t1 (column1 varchar2(100), column2 varchar2(100));
insert into t1 values ('HELLO', 'WORLD');
insert into t1 values ('SAY', 'HELLO');
insert into t1 values ('SOMETHING', 'ELSE');
insert into t1 values ('WORLD', 'CUP');
commit;

Create the datastore with the desired columns:

begin
ctx_ddl.create_preference('t1_multi_column_datastore', 'MULTI_COLUMN_DATASTORE');
ctx_ddl.set_attribute('t1_multi_column_datastore', 'columns', 'column1, column2');
end;
/

Create the Text index using this datastore:

create index i1 on t1 (column1) indextype is ctxsys.context
  parameters ('datastore t1_multi_column_datastore');

Or if you want the index maintained on commit:

create index i1 on t1 (column1) indextype is ctxsys.context
  parameters ('datastore t1_multi_column_datastore sync(on commit)');

Searching for the words HELLO or WORLD:

select * from t1 where contains (column1, 'HELLO or WORLD') > 0;

COLUMN1    COLUMN2  
---------- ----------
HELLO      WORLD     
SAY        HELLO     
WORLD      CUP

Notice that you do not need to specify all columns in the query, but you need to specify an operator between words.

Best Answer

Related Solutions

Postgresql – What does “GIN doesn’t support full index scans” mean

Oracle “matches” multiple columns

Related Question