Why function similarity return 1 while 2 value not the same?-CodePudding

Value in databbase: fullname="This is a pen."

My query: SELECT SIMILARITY(fullname,'This is a a pen') as sl from users

Result: sl=1

Why function similarity return 1 while 2 value not the same?

How can return 1 when value is the same. If value not the same return < 1?

CodePudding user response：

The reason is that the period is stripped from the string when trigrams are created. Also, if a trigram occurs more than once (like from your ' a a ', these duplicates are ignored:

SELECT show_trgm('This is a a pen.');

                                 show_trgm                                 
═══════════════════════════════════════════════════════════════════════════
 {"  a","  i","  p","  t"," a "," is"," pe"," th","en ",his,"is ",pen,thi}
(1 row)

To test for equality, use the = operator. Perhaps something like this will satisfy:

ORDER BY fullname = 'This is a a pen' DESC,
         similarity(fullname, 'This is a a pen') DESC