postgresql Pgsql 错误：您可能需要添加显式类型转换
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/42149496/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Pgsql error: You might need to add explicit type casts
提问by Christian
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.
我的网站运行良好，直到我将它部署到 heroku，问题是 heroku 使用 pgsql 而我使用的是 mysql 和 laravel 框架。
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
这是我将它部署到 heroku 时得到的
SQLSTATE[42883]: Undefined function: 7 ERROR: operator does not exist: integer ~~* unknown
LINE 1: ...ient_id" = "patients"."PatientID" and "vaccine_id" ILIKE $3)
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 %))
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.
我试过使用像 CAST(vaccine_id AS VARCHAR) 这样的演员表，但我没有收到错误，但它没有返回任何结果。
回答by Roman Tkachuk
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 '='
看起来vaccine_id是整数，并且您不能使用运算符ILIKE来整数。尝试只是 '='
If you want to use LIKE, ILIKE or other text operator you must cast your data to text. In SQL it must looks like:
如果要使用 LIKE、ILIKE 或其他文本运算符，则必须将数据转换为文本。在 SQL 中，它必须如下所示：
WHERE "vaccine_id"::text ILIKE val
instead
反而
WHERE "vaccine_id" ILIKE val
回答by Mahesh Kathiriya
You could do this:
你可以这样做：
$q->where('cast(vaccine_id AS VARCHAR)','LIKE','%' . $vaccine_id)
OR
或者
$q->where('cast(vaccine_id AS TEXT)','LIKE','%' . $vaccine_id)
