compare absolute value with double value present in table using PostgreSQL

Question

select id from records where (mean_logratio = -4.81)

-4.810215473175049 value is present in the table which supposed to be fetched

-4.810215473175049 only exact value is accepted not absolute value, in search query for = and != condition

for absolute value like -4.81, not getting expected results

Answer 1

You can go with either approach:

1. If you want to compare after rounding off upto two decimal place.

select distinct(workflowid) 
from cyto_records r join cyto_record_results rr on (r.recordid = rr.recordid) 
where (round(rr.mean_logratio::numeric,2) = -4.81) 

2. If you want to truncate upto two decimal and compare then use below mentioned query:

select distinct(workflowid) 
from cyto_records r join cyto_record_results rr on (r.recordid = rr.recordid)
where (trunc(rr.mean_logratio::numeric,2) = -4.81) 

In case of data type mismatch error, you may need to cast you data.