Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.

asteway's avatar

Problem with postgresql 'where' function SQLSTATE[22P02]: Invalid text representation: 7 ERROR: invalid input syntax for integer:

A simple query

            Jobfieldofstudy::where("id", $value)->delete();

on postgresql database is returning error

QueryException in Connection.php line 729: SQLSTATE[22P02]: Invalid text representation: 7 ERROR: invalid input syntax for integer: "jobfieldofstudies" (SQL: select * from "jobs" where "jobs"."id" = jobfieldofstudies limit 1)

My migration looks like this:

public function up()
{
    Schema::create('jobfieldofstudys', function (Blueprint $table) {
        $table->increments('id');
        $table->integer('jobs_id')->unsigned();
        $table->foreign('jobs_id')->references('id')->on('jobs');
        $table->integer('fieldofstudys_id')->unsigned();
        $table->foreign('fieldofstudys_id')->references('id')->on('fieldofstudys');
        $table->timestamps();
    });
}

Any help would be appreciated.

0 likes
8 replies
tisuchi's avatar

Its not because of your simple query like this-

Jobfieldofstudy::where("id", $value)->delete();

Its for reference part-

$table->foreign('jobs_id')->references('id')->on('jobs');
asteway's avatar

thanks for your reply, but I am still not seeing where the problem is...

asteway's avatar

I am getting this error everywhere I use the 'where' clause.

ex.

SQLSTATE[22P02]: Invalid text representation: 7 ERROR: invalid input syntax for integer: "HelloCareer" (SQL: select * from "access_tokens" where "access_tokens"."id" = HelloCareer limit 1)

Here's the full stacktrace.

in Connection.php line 729 at Connection->runQueryCallback('select * from "access_tokens" where "access_tokens"."id" = ? limit 1', array('HelloCareer'), object(Closure)) in Connection.php line 685 at Connection->run('select * from "access_tokens" where "access_tokens"."id" = ? limit 1', array('HelloCareer'), object(Closure)) in Connection.php line 349 at Connection->select('select * from "access_tokens" where "access_tokens"."id" = ? limit 1', array('HelloCareer'), true) in Builder.php line 1610 at Builder->runSelect() in Builder.php line 1596 at Builder->get(array('')) in Builder.php line 625 at Builder->getModels(array('')) in Builder.php line 316 at Builder->get(array('')) in Builder.php line 286 at Builder->first(array('')) in Builder.php line 167 at Builder->find('HelloCareer') at call_user_func_array(array(object(Builder), 'find'), array('HelloCareer')) in Model.php line 3526 at Model->__call('find', array('HelloCareer')) at call_user_func_array(array(object(Access_token), 'find'), array('HelloCareer')) in Model.php line 3540 at Model::__callStatic('find', array('HelloCareer')) in SMSOauth2Controller.php line 18 at SMSOauth2Controller->getAccessToken('HelloCareer', 'St85UchczVuSlVCtpUEl', '5r3nZNbScW1aXW5Kx5xOd86', 'JXpd9NC8WFYbDLaEwJHfr4Xsy6X6szfqyuq') in SMSOauth2Controller.php line 82 at SMSOauth2Controller->SendSms() at call_user_func_array(array(object(SMSOauth2Controller), 'SendSms'), array()) in Controller.php line 80 at Controller->callAction('SendSms', array()) in ControllerDispatcher.php line 146 at ControllerDispatcher->call(object(SMSOauth2Controller), object(Route), 'SendSms') in ControllerDispatcher.php line 94 at ControllerDispatcher->Illuminate\Routing{closure}(object(Request)) at call_user_func(object(Closure), object(Request)) in Pipeline.php line 52 at Pipeline->Illuminate\Routing{closure}(object(Request)) at call_user_func(object(Closure), object(Request)) in Pipeline.php line 102 at Pipeline->then(object(Closure)) in ControllerDispatcher.php line 96 at ControllerDispatcher->callWithinStack(object(SMSOauth2Controller), object(Route), object(Request), 'SendSms') in ControllerDispatcher.php line 54 at ControllerDispatcher->dispatch(object(Route), object(Request), 'App\Http\Controllers\SMSOauth2Controller', 'SendSms') in Route.php line 174 at Route->runController(object(Request)) in Route.php line 140 at Route->run(object(Request)) in Router.php line 724 at Router->Illuminate\Routing{closure}(object(Request)) at call_user_func(object(Closure), object(Request)) in Pipeline.php line 52 at Pipeline->Illuminate\Routing{closure}(object(Request)) in Authenticate.php line 28 at Authenticate->handle(object(Request), object(Closure)) at call_user_func_array(array(object(Authenticate), 'handle'), array(object(Request), object(Closure))) in Pipeline.php line 136 at Pipeline->Illuminate\Pipeline{closure}(object(Request)) at call_user_func(object(Closure), object(Request)) in Pipeline.php line 32 at Pipeline->Illuminate\Routing{closure}(object(Request)) in VerifyCsrfToken.php line 64 at VerifyCsrfToken->handle(object(Request), object(Closure)) at call_user_func_array(array(object(VerifyCsrfToken), 'handle'), array(object(Request), object(Closure))) in Pipeline.php line 136 at Pipeline->Illuminate\Pipeline{closure}(object(Request)) at call_user_func(object(Closure), object(Request)) in Pipeline.php line 32 at Pipeline->Illuminate\Routing{closure}(object(Request)) in ShareErrorsFromSession.php line 49 at ShareErrorsFromSession->handle(object(Request), object(Closure)) at call_user_func_array(array(object(ShareErrorsFromSession), 'handle'), array(object(Request), object(Closure))) in Pipeline.php line 136 at Pipeline->Illuminate\Pipeline{closure}(object(Request)) at call_user_func(object(Closure), object(Request)) in Pipeline.php line 32 at Pipeline->Illuminate\Routing{closure}(object(Request)) in StartSession.php line 64 at StartSession->handle(object(Request), object(Closure)) at call_user_func_array(array(object(StartSession), 'handle'), array(object(Request), object(Closure))) in Pipeline.php line 136 at Pipeline->Illuminate\Pipeline{closure}(object(Request)) at call_user_func(object(Closure), object(Request)) in Pipeline.php line 32 at Pipeline->Illuminate\Routing{closure}(object(Request)) in AddQueuedCookiesToResponse.php line 37 at AddQueuedCookiesToResponse->handle(object(Request), object(Closure)) at call_user_func_array(array(object(AddQueuedCookiesToResponse), 'handle'), array(object(Request), object(Closure))) in Pipeline.php line 136 at Pipeline->Illuminate\Pipeline{closure}(object(Request)) at call_user_func(object(Closure), object(Request)) in Pipeline.php line 32 at Pipeline->Illuminate\Routing{closure}(object(Request)) in EncryptCookies.php line 59 at EncryptCookies->handle(object(Request), object(Closure)) at call_user_func_array(array(object(EncryptCookies), 'handle'), array(object(Request), object(Closure))) in Pipeline.php line 136 at Pipeline->Illuminate\Pipeline{closure}(object(Request)) at call_user_func(object(Closure), object(Request)) in Pipeline.php line 32 at Pipeline->Illuminate\Routing{closure}(object(Request)) at call_user_func(object(Closure), object(Request)) in Pipeline.php line 102 at Pipeline->then(object(Closure)) in Router.php line 726 at Router->runRouteWithinStack(object(Route), object(Request)) in Router.php line 699 at Router->dispatchToRoute(object(Request)) in Router.php line 675 at Router->dispatch(object(Request)) in Kernel.php line 246 at Kernel->Illuminate\Foundation\Http{closure}(object(Request)) at call_user_func(object(Closure), object(Request)) in Pipeline.php line 52 at Pipeline->Illuminate\Routing{closure}(object(Request)) in CheckForMaintenanceMode.php line 44 at CheckForMaintenanceMode->handle(object(Request), object(Closure)) at call_user_func_array(array(object(CheckForMaintenanceMode), 'handle'), array(object(Request), object(Closure))) in Pipeline.php line 136 at Pipeline->Illuminate\Pipeline{closure}(object(Request)) at call_user_func(object(Closure), object(Request)) in Pipeline.php line 32 at Pipeline->Illuminate\Routing{closure}(object(Request)) at call_user_func(object(Closure), object(Request)) in Pipeline.php line 102 at Pipeline->then(object(Closure)) in Kernel.php line 132 at Kernel->sendRequestThroughRouter(object(Request)) in Kernel.php line 99 at Kernel->handle(object(Request)) in index.php line 53

rumm.an's avatar

Seems like you are providing a string in $value. But in where method you are trying to compare id which itself is an integer with the $value which is string. Why don't you test this with some hard-coded integer value that exists in your table just to test.

Jobfieldofstudy::where("id", 2)->delete(); // assuming there exists a record with id=2
1 like
sudo255's avatar

Great solution, I don't know why my variable gets rendered as a string but I have gotta type-cast it to an Integer. Thanks Man

ranto's avatar

I was having the same problem.

Suddenly I notice something on my routes/web.php file

I was using routes like

...
Route::get('/base/{id}', 'controller@function1');
Route::get('/base/something', 'controller@function2');
...

And with these order, I was slapped with an error that uses my web url to search an id.

To solve this, I just swap the order of the url

...
Route::get('/base/something', 'controller@function2');
Route::get('/base/{id}', 'controller@function1');
...

I think when you declare /base/{id}, all the url below that, enter to the {id} tag. Thats bc I get the las path of the url on the error.

Edit: Sorry if my english isn't that good. Not mother lenguage.

alikalan's avatar

this problem is for your Route you use a route like

$router->get('post/{id}','postController@show') then you define another Route like:

$router->get('post/any_thing','postController@show')

and the problems caused

Please or to participate in this conversation.