I have a query like this :
Select office_name, ofc_code
from table1
where ofc_code in (select ofc_institution from table2 where id ='2')
If i run this i get an error:
Operator does not exist:numeric = character varying
Hint : no operator matches the given name and argument types
In the above query: ofc_code is numeric and ofc_institution is a character varying
Don't compare apples to oranges. '2' is a string value, 2 is a number.
You also need to convert ofc_institution to a number if you are certain that it only contains numbers (the question then is: why on earth are you storing numbers in a varchar column?):
Select office_name, ofc_code
from table1
where ofc_code in (select ofc_institution::numeric
from table2
where id = 2);
If you can't be certain that ofc_institution is always a number, then cast the ofc_code to a string - but that then begs the question why are you comparing those columns to begin with:
Select office_name, ofc_code
from table1
where ofc_code::varchar in (select ofc_institution
from table2
where id = 2);
psql session, just do a \d+ table1 and \d+ table2 and look at the data types of columns table1.ofc_code, table2.ofc_institution and table2.id. That should make you understand what is wrong with this query and others.