0

I am using Postgres' full text search to search the "title" column across my records. The results are sorted by their ts_rank for rendering.

It works except an exact match is not treated any special even though to a human it is a perfect match so should have the highest rank (of 1) but it isn't.

DB fiddle: https://www.db-fiddle.com/f/2L3Tstgj5iP6FmZPVg6JUL/0

Expected result: "Happy" has a rank of 1 instead of 0.6079271

Do I need to combine my FTS with a LIKE/ILIKE statement? Why is an exact match not a higher rank?

1
  • The manual states "PostgreSQL provides two predefined ranking functions, which take into account lexical, proximity, and structural information; that is, they consider how often the query terms appear in the document, how close together the terms are in the document, and how important is the part of the document where they occur". You would need to supply your own ranking function in order to do something different.
    – user1822
    Commented Oct 12, 2022 at 9:18

1 Answer 1

0

I don't know what your query is, but an adjusted ORDER BY clause might help:

ORDER BY textcol LIKE '%search phrase%' DESC,
         ts_rank(...)

This will always rank rows that contain the search phrase exactly first.

4
  • My query is in the DB fiddle.
    – Jared
    Commented Oct 12, 2022 at 10:13
  • I see no call to ts_rank there... Commented Oct 12, 2022 at 10:29
  • It's inside the function declaration searchAssets. DB fiddle required that I declare it as a string.
    – Jared
    Commented Oct 12, 2022 at 10:50
  • I see. Well, does my answer work for you? Commented Oct 12, 2022 at 10:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.