0

I have a use case in which i want to search a company by it's name or the name of the division. If i put in division name in the search query it should return name of the companies which have that division.

To achieve this i created a table with the company name and a column with concatenated name of the company and names of the divisions split by spaces.

First, i tried full text search on the column but in FTS query the results are returned only when there is an exact match with the vectors created by the ts_vector function.

Then i tried search using trigrams which gave better results but when i type the name of a company with the name of any division i don't get results because the trigrams created are not in the same order of the search query.

What can i do to improve my search on the table?

Paolo Melchiorre
  • 5,716
  • 1
  • 33
  • 52
vasu gaur
  • 63
  • 2
  • 5

1 Answers1

0

Why not create the helper table like this and use a simple select instead of Full-Text-Search?

CREATE TABLE company_keywords (
  company VARCHAR PRIMARY KEY,
  keyword VARCHAR NOT NULL
);

SELECT DISTINCT company FROM company_keywords WHERE keyword LIKE 'XYZ%';
Lupf
  • 148
  • 1
  • 6