Postgresql – pls give the solution . this query will not work


select * 
from masters."RCM" 
where id in (select rcm_id 
             from masters."TASK" 
             where id in (select audit_details->>'task_id' 
                          from masters."AUDIT" 
                          where id=43))

apply after this code some error will occured.

ERROR:  operator does not exist: bigint = text
LINE 5:              where id in (select audit_details->>'task_id' 
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.
SQL state: 42883
Character: 115

Best Answer

"TASK".id is a bigint value, but audit_details->>'task_id' returns a text (varchar) value. Postgres prevents you from comparing apples to oranges, so you can't compare those two things directly.

As the error message suggests you need to add an explicit type cast.

where id in (select (audit_details->>'task_id')::bigint
             from ...)