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
postgresql9.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
removedallows the query to work) is comparing two values in two tables:
>
>
> Ben
>
> The full query is :
>
> {{{
> 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';
> }}}
>
Follow up questions:
1) Where is this query being run from?
2) Why are the integers at the end of the query quoted?
--
Adrian Klaver
adrian.klaver@gmail.com