Re: ERROR: invalid input syntax for integer: ""

On 02/05/2013 08:24 PM, Ben Madin wrote:
> G'day,
>
> I hope to be shown to be an idiot, but we are receiving the message
>
> ERROR: invalid input syntax for integer: ""
>
> when using a pl/pgsl function with some quite complex queries that seem to be working on a developer machine using postgresql 9.1.6, but not on the production machine using 9.1.7.
>
> The source of our confusion is stemming from the fact that the offending line (being the join predicate that if removed allows the query to work) is comparing two values in two tables:
>
> ...
> FROM reports rep
> LEFT JOIN results res
> ON res.reportid = rep.id <== this line is causing the error to be returned
> AND res.resulttypeid = 108
> AND res.del = false
> …

>
>
> My questions then are :
>
> Given that the join is between two integer columns, how could it be an invalid syntax for one of them?
>
> Given the query is working on one machine (using a copy of the database downloaded and imported from the second machine last night) running 9.1.6, is there any reason it wouldn't work on the original machine - have there been any changes in casting that I didn't notice between 9.1.6 and 9.1.7?

I am not seeing anything obvious.
Is there a chance the import to the second machine did not go well?
Have you looked at the values for id and reportid to see if they look
alright?

>
> cheers
>
> Ben
>

--
Adrian Klaver
adrian(dot)klaver(at)gmail(dot)com