org.postgresql.util.PSQLException: ERROR: invalid input syntax for integer: “”

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.

Like this post? Don’t forget to share it!