CODEWITHSUNDEEP
postgresqlfull-text-search
Peter Gerdes
Peter Gerdes

Reputation: 3008

Return full preciscion of rank in postgres fulltext search query

I'm using postgres fulltext search in a context where I use lazy loading to load more rows. Therefore, I'm returning the rank and the id of the element returned and trying to search for those objects where (rank, id) < (last_rank, last_id). Unfortunately, postgres seems to have a larger internal representation of the rank than it returns in the select. For instance, compare these two queries

SELECT posts.id, ts_rank((textsearch || aux_textsearch), websearch_to_tsquery('en', 'test')) FROM posts WHERE posts.id = 'c2459b96-35af-41a7-ad08-11f4e8239c71' AND (ts_rank((textsearch || aux_textsearch), websearch_to_tsquery('en', 'test')), posts.id) < (.6079271, 'c2459b96-35af-41a7-ad08-11f4e8239c71') ;

This returns

                  id                  |  ts_rank
--------------------------------------+-----------
 c2459b96-35af-41a7-ad08-11f4e8239c71 | 0.6079271

However, if I replace the explicitly given rank with the actual computed rank it gives no results as one expects.

SELECT posts.id, ts_rank((textsearch || aux_textsearch), websearch_to_tsquery('en', 'test')) FROM posts WHERE posts.id = 'c2459b96-35af-41a7-ad08-11f4e8239c71' AND (ts_rank((textsearch || aux_textsearch), websearch_to_tsquery('en', 'test')), posts.id) < (ts_rank((textsearch || aux_textsearch), websearch_to_tsquery('en', 'test')), 'c2459b96-35af-41a7-ad08-11f4e8239c71') ;

How can I get postgres to return the full preciscion it uses to compute the rank? Or should I just accept that the digits beyond a certain point aren't going to matter and use a rounding function (thereby giving up any ordering information past that point)? And if I do round what data type should I case the rank to? Numeric? Does it matter?

Upvotes: 0

Views: 94

Answers (1)

jjanes
jjanes

Reputation: 44305

I believe the problem to be that the constant is implicitly treated as "double precision", while ts_rank returns a "real". If you write the constant as 0.6079271::real then it should do what you want;

Upvotes: 1

Related Questions