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:
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@xxxxxxxxx
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
