Postgresql – to_tsquery – Does it always accept quert text

full-text-searchpostgresql

I am very new to FTS features in Postgres, in fact, have started exploring them since yesterday only.

I wanted to pass list of values stored in columns of a DB tables instead of query text in to_tsquery.

Something like this:

select  * 
from t1
 where to_tsvector("col1") @@ to_tsquery((select col2 from t2 where id = 123))

Not sure if this is a possibility? Is there any alternative by which such kind of implementation can be achieved using to_tsquery?

Best Answer

If the values can be arbitrary strings, you can get errors:

SELECT to_tsquery('a&');
ERROR:  no operand in tsquery: "a&"
SELECT to_tsquery('''q');
ERROR:  syntax error in tsquery: "'q"

Use plainto_tsquery, which is designed to accept plain text strings.

Related Solutions

Sql-server – Does full text search works with characters

CONTAINS and LIKE are two completely different beasts.

As it is a full text index search the wildcards (% %) become redundant.

For your query I belive you would need to do something like:

SELECT * 
FROM AMPP AS A
WHERE CONTAINS(A.NM, '"Amoxici*"');

Refer to MS Books Online

PostgreSQL – Filtering Special Characters in to_tsquery

You may want to just leave it as users enter it, since you can safely use strings processed twice via quote_literal. In the to_tsquery documentation, the example for phrases within ts queries is:

SELECT to_tsquery('''supernovae stars'' & !crab');

Adding some junk to the phrase breaks it, unless you pass it twice via quote_literal, which makes any string safe to use:

SELECT to_tsquery(quote_literal(quote_literal('supernovae '':|stars')) || ' & !crab');

Best Answer

Related Solutions

Sql-server – Does full text search works with characters

PostgreSQL – Filtering Special Characters in to_tsquery

Related Question