ERROR: invalid input syntax for integer: ""

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
…

I have included the full query executed by the function at the bottom of the email.

In the first it is an integer primary key, in the second a not null integer, as shown below:

Table "data.reports"
Column | Type | Modifiers
---------------+--------------------------+------------------------------------------------------
id | integer | not null default nextval('reports_id_seq'::regclass)
projectid | integer |
…
Indexes:
"reports_pkey" PRIMARY KEY, btree (id)

Table "data.results"
Column | Type | Modifiers
---------------+--------------------------+------------------------------------------------------
id | integer | not null default nextval('results_id_seq'::regclass)
reportid | integer | not null
…

Indexes:
"results_pkey" PRIMARY KEY, btree (id)
"results_del_btree" btree (del)
"results_idx_reportid" btree (reported)

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?

cheers

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';
}}}

--

Ben Madin

m : +61 448 887 220
e : ben(at)ausvet(dot)com(dot)au