Recently I was working on a project where I faced an issue. A column in a GUI was sorting properly for sometime but stopped working suddenly one day. Let us start debugging the issue.
Exception
org.postgresql.util.PSQLException: ERROR: invalid input syntax for integer: ""
Problem Code
So we starting looking at the code and found a query quite similar as below causing the above exception.
Table to be sorted:
create table studentgroup(students varchar);
Query executed to sort.
select students from studentgroup order by cast(students as int);
Solution
As you can see from the above query it is trying to cast studentgroup to int. But we looked in the db there was an empty value. And the query was no longer able sort on studentgroup as one of the values could be casted to int.
To get rid of the error the query needs to be changed to
select students from studentgroup order by cast(coalesce(nullif(students ,''),'-1') as int);
Let us now look at the various parts of the query
NULLIF(value1, value2)
The NULLIF function returns a null value if value1 equals value2; otherwise it returns value1.
In our example, nullif(students,”) returns null for empty strings
COALESCE(value [, …])
The COALESCE function returns the first of its arguments that is not null.
In our example, coalesce(nullif(students,”),’-1′) returns -1 for empty strings
Conclusion:
So the error was suddenly raised as the GUI validation for only entering numbers for this field had failed and an empty string had made its way to the database. The cleanest solution is to define the number column as a number and not as any other datatype.
