ERROR: invalid input syntax for integer: "" - Mailing list pgsql-general

G'day,

I hope to be shown to be an idiot, but we are receiving the message=20

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=20
ON res.reportid =3D rep.id         <=3D=3D this line is causing the =
error to be returned
AND res.resulttypeid =3D 108=20
AND res.del =3D false
=85

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                      =20
=
---------------+--------------------------+-------------------------------=
-----------------------
 id            | integer                  | not null default =
nextval('reports_id_seq'::regclass)
 projectid     | integer                  |=20
=85
Indexes:
    "reports_pkey" PRIMARY KEY, btree (id)



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

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('0101000020BB1000008716D9CEF7A36240643BDF4F8DA741=
C0', post.the_point::geometry)/1000) as dist=20
FROM reports rep=20
LEFT JOIN users u ON rep.link =3D u.id=20
LEFT JOIN postcodes post ON u.postcode::integer =3D post.postcode =20
LEFT JOIN species spe ON rep.species::text like spe.speccode::text AND =
spe.synonym =3D 0 =20
LEFT JOIN results res8 ON res8.reportid =3D rep.id AND res8.resulttypeid =
=3D 108 AND res8.del =3D false
LEFT JOIN resultlookup rlu8 ON rlu8.resulttypesid =3D 108 AND rlu8.id =3D =
res8.resultvalue::int
WHERE rep.del IS false AND rep.projectid =3D 51=20
AND round(st_distance_sphere( =
'0101000020BB1000008716D9CEF7A36240643BDF4F8DA741C0', =
post.the_point)/1000) < '150' AND spe.id =3D '9465' AND rlu8.id =3D =
'935';
}}}





--=20

Ben Madin

m : +61 448 887 220
e : ben@ausvet.com.au