How to search many values with Postgres trigram?

Question

If I have a table with the values name and surname, how do I search for those two values?

Example

I set a threshold to filter out unwanted values, and then I can find similarity with table name and searched text; however, if there is only name or surname, it's OK; but if both are combined, the similarity is different.

But in function similarity I am able to put only one value name or surname

SET pg_trgm.similarity_threshold = 0.8;

SELECT name, surname, similarity(name, 'seaching text') AS sml
  FROM goods_goods
  WHERE name %  'seaching text'
  ORDER BY sml DESC, name

How can I put more values in a similar function? Like this similarity((name,surname and so on if necessary), 'seaching text')

---

EDITED

One More Example To Clarify

• Table name is table

• There is three columns sentence1, sentence2, sentence3

I want to find the most similar row in which the searching string is most similar to sentence1, sentence2, sentence3 All in one, not separated sentences.

Here an example with some

table

|      sentence1    |   sentence2   |      sentence3     |
|-------------------|---------------|--------------------|
|  I have an apple  |  Samsung Tv   |  Dji mavic 2 Zoom  |
|-------------------|---------------|--------------------|
|  Tiger is red!!!  |  postgresql   |  Dji mavic 2 Zoom  |
|-------------------|---------------|--------------------|
|  Basketball ABCD  |  battery AC   |  Dji mavic 3 Zoom  |
|-------------------|---------------|--------------------|
|  Tiger is red!!!  |  postgresql   |  Dji mavic 3 Zoom  |

and now my seraching text is something like Tiger postgres dji mavic 2

As we can see most similar is row number 2 --> 61% but row 4 is 57% it's correct because there is ...mavic 3 but we want ...mavic 2

select strict_word_similarity('I have an apple Samsung Tv Dji mavic 2 Zoom','Tiger postgres dji mavic 2' ) --> 0.27906978

select strict_word_similarity('Tiger is red!!! postgresql Dji mavic 2 Zoom','Tiger postgres dji mavic 2' ) --> 0.61904764

select strict_word_similarity('Basketball ABCD battery AC Dji mavic 3 Zoom','Tiger postgres dji mavic 2' ) --> 0.24390244

select strict_word_similarity('Tiger is red!!! postgresql Dji mavic 2 Zoom','Tiger postgres dji mavic 2' ) --> 0.5714286

But if we compare each column one by one, there are columns  which is similar, but all  sentences together is completely wrong. Like row one and two in sentence3 have same text, but first row is not what I want.

So How can i do that with pg_trgm?

Answer 1

It sounds to me like you want this:

SELECT name, surname, greatest(similarity(name, 'seaching text'),similarity(surname, 'seaching text'))  AS sml
FROM goods_goods
WHERE name % 'seaching text' or surname % 'seaching text' 
ORDER BY sml DESC, name