`ts_rank` is returning a numeric/decimal/real that fails a “greater than” check

Posted on

Question :

The following yields (seemingly exactly) 0.1:

select ts_rank_cd(to_tsvector('cat'), to_tsquery('cat'));

Yet the following yields t:

select ts_rank_cd(to_tsvector('cat'), to_tsquery('cat')) > 0.1;

I would expect it to yield t if the operation was >=, not > How can I get the “real” value of the ts_rank_cd so that it returns f?


As background, I’m writing a full text search query that uses keyset pagination with ts_rank, so being able to order results based on a concrete, precise value of ts_rank is important. With keyset pagination, the client provides the last result from the current page as a parameter and asks for the next page. Currently, when my client passes back 0.1, the above > behavior causes erroneous results

Answer :

ts_rank_cd yields a real. If you cast this to double precision, the result becomes:

SET extra_float_digits = 3;

SELECT ts_rank_cd(to_tsvector('cat'), to_tsquery('cat'))::double precision;

     ts_rank_cd      
---------------------
 0.10000000149011612
(1 row)

That explains what you observe, because the real is cast to double precision if you compare it to a double precision number.

The solution is to explicitly cast 0.1 to real, so that real comparison is used:

SELECT ts_rank_cd(to_tsvector('cat'), to_tsquery('cat')) = 0.1::real;

 ?column? 
----------
 t
(1 row)

Leave a Reply

Your email address will not be published. Required fields are marked *