1

I have below plainto_tsquery query as part of full text searching on a PostgreSQL table.

SELECT plainto_tsquery('english', 'flat discount on flight_tickets');

This query will return 'flat' & 'discount' & 'flight' & 'ticket'

Is there any way to make it to return 'flat' & 'discount' & 'flight_ticket'

Just to avoid closing ticket by marking as duplicate, I have checked below stackoverflow questions

Mithun Sreedharan
  • 49,883
  • 70
  • 181
  • 236
  • 1
    Does this answer your question? [Escaping special characters in to\_tsquery](https://stackoverflow.com/questions/14103880/escaping-special-characters-in-to-tsquery) – Tim Biegeleisen Jan 14 '20 at 05:12
  • @TimBiegeleisen, No. https://stackoverflow.com/questions/16020164/psqlexception-error-syntax-error-in-tsquery/16020565#16020565 aslo didn't help – Mithun Sreedharan Jan 14 '20 at 05:38

1 Answers1

2

You could "pre-process" the text and the queries to replace _ with a different character like /.

CREATE INDEX ON texts USING gin (to_tsvector('english', replace(doc, '_', '/')));

SELECT * FROM texts
WHERE to_tsvector('english', replace(doc, '_', '/'))
      @@ plainto_tsquery('english', replace('flat discount on flight_tickets', '_', '/'));
Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
  • Could you please explain a bit on the indexing part, especially that `doc`. My current query is of the form `SELECT * FROM promotions_table WHERE to_tsvector(promotions_table::text) @@ plainto_tsquery('flat discount on flight_tickets')); ` which returns some records and `SELECT * FROM promotions_table WHERE to_tsvector(promotions_table::text) @@ plainto_tsquery( replace('flat discount on flight_tickets', '_', '/'));` is returning empty set – Mithun Sreedharan Jan 14 '20 at 08:08
  • Of course you also have to use `replace` in the argument to `to_tsvector`, like I wrote in my answer. – Laurenz Albe Jan 14 '20 at 12:32