How to find column name for invalid text representation 7 error invalid input syntax for integer in laravel?

Question

e.g., I have a table...

create table emp(  
  empno    integer,  
  ename    character varying(255),  
  job      character varying(255),  
  mgr      integer,  
  hiredate timestamp without time zone,  
  sal      double pricision,  
  comm     integer,  
  deptno   integer
)

When I try to insert the data...

insert into emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) values (1,'abc','abc','e','2018-10-12 00:00:00',50.50,1,'f');

Then it's giving me an error of invalid text representation 7 error invalid input syntax for integer because I have inserted string value for mgr and deptno this can be found only by viewing the values, but PostgreSQL is not giving me the exact column name in which it has encounter such issue. Is there any way to get column name in the error message itself. Because when we have more than 50 to 60 columns, then it's challenging to find the column which has the issue.

Answer 1

You don't have to find out which column is wrong because you should make sure your inputs are properly formatted BEFORE inserting in the database.

You are using Laravel, so you should make use of the great toolset it offers for validation: https://laravel.com/docs/5.8/validation

For example in your case, you should validate mgr by using the following rule at the start of your controller:

$request->validate([
    'mgr' => 'number'
]);

This will throw an exception if the rule is not respected, and thus will fail when the user is trying to input data, not when your backend tries to insert the data in the DB.

Answer 2

You can catch the sql query exception using class \Illuminate\Database\QueryException and can customize the sql exception as per your requirements.

try {
    // Add your insert query here.

} catch (\Illuminate\Database\QueryException $e) {

    // Here you can get the sql exception details.
    dd($e->getMessage(), $e->errorInfo); // print for the details info.

    // customzie you error

} catch (\Exception $e) {

    // for the other exception.
    dd($e->getMessage(), $e->errorInfo); // print for the details info.
}

You can also use the Laravel Exception Handling to customize the message at common place.

https://laravel.com/docs/5.8/errors