Pgsql error: You might need to add explicit type casts

Question

My website is just working fine til i deployed it to heroku and the problem is heroku uses pgsql and I'm using mysql and laravel framework.

my query is

$patient = Patient::where('patient_address', 'ILIKE' ,'%' . $request->input)->where('patient_sex', 'ILIKE' ,'%' . $request->gender)->whereHas('users', function($q) use($vaccine_id){
        $q->where('vaccine_id','ILIKE','%' . $vaccine_id);
    })->get();

here's what I'm getting when I deploy it to heroku

SQLSTATE[42883]: Undefined function: 7 ERROR: operator does not exist: integer ~~* unknown LINE 1: ...ient_id" = "patients"."PatientID" and "vaccine_id" ILIKE $3)

HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts. (SQL: select * from "patients" where "patient_address" ILIKE %San Francisco and "patient_sex" ILIKE % and exists (select * from "vaccines" inner join "immunizations" on "vaccines"."VaccineID" = "immunizations"."vaccine_id" where "immunizations"."patient_id" = "patients"."PatientID" and "vaccine_id" ILIKE %))

I have tried using cast like CAST(vaccine_id AS VARCHAR) and I' not getting the error but it doesnt return any result.

I don't know this API well, but where "patient_address" ILIKE %San Francisco is wrong, San Francisco should be in single quotes AFAIK. — Tim Biegeleisen
it's a variable $request->input = san fancisco how do i put single quote on it? — Christian
I'm not sure, only I can tell you that if the query output is verbatim, then it's wrong. — Tim Biegeleisen

Roman Tkachuk Roman Tkachuk · Accepted Answer · 2017-02-10T05:52:46

The problem is here:

$q->where('vaccine_id','ILIKE','%' . $vaccine_id)

looks like vaccine_id is integer, and you can not use operator ILIKE to integer. Try just '='

If you want to use LIKE, ILIKE or other text operator you must cast your data to text. In SQL it must looks like:

WHERE "vaccine_id"::text ILIKE val

instead

WHERE "vaccine_id" ILIKE val

Pgsql error: You might need to add explicit type casts

2 Answers