Ben Madin <ben@ausvet.com.au> writes:
> I hope to be shown to be an idiot, but we are receiving the message
> ERROR: invalid input syntax for integer: ""
The only part of this query that looks like it could possibly produce
that error is the res8.resultvalue-to-int cast:
> SELECT rep.id, res8.reportid, round(st_distance_sphere('0101000020BB1000008716D9CEF7A36240643BDF4F8DA741C0',
post.the_point::geometry)/1000)as dist
> FROM reports rep
> LEFT JOIN users u ON rep.link = u.id
> LEFT JOIN postcodes post ON u.postcode::integer = post.postcode
> LEFT JOIN species spe ON rep.species::text like spe.speccode::text AND spe.synonym = 0
> LEFT JOIN results res8 ON res8.reportid = rep.id AND res8.resulttypeid = 108 AND res8.del = false
> LEFT JOIN resultlookup rlu8 ON rlu8.resulttypesid = 108 AND rlu8.id = res8.resultvalue::int
^^^^^^^^^^^^^^^^^^^^^
> WHERE rep.del IS false AND rep.projectid = 51
> AND round(st_distance_sphere( '0101000020BB1000008716D9CEF7A36240643BDF4F8DA741C0', post.the_point)/1000) < '150' AND
spe.id= '9465' AND rlu8.id = '935';
> }}}
Presumably, there are some empty strings in results.resultvalue, and if
the query happens to try to compare one of them to rlu8.id, kaboom.
The way that the error comes and goes depending on seemingly-irrelevant
changes isn't too surprising. Probably what's happening is that the
query plan changes around so that that test occurs earlier or later
relative to other join clauses.
regards, tom lane