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

Thanks Tom,

On 2013-02-06, at 13:42 , Tom Lane <tgl@sss.pgh.pa.us> wrote:

> The only part of this query that looks like it could possibly produce
> that error is the res8.resultvalue-to-int cast:

>> 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
>                                                                        =
^^^^^^^^^^^^^^^^^^^^^
> 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.


Yes - this would be the case if it tried to match it against the =
resultvalue only - some of the values in the table are NULL, but not for =
this resulttypeid.

So my understanding, working left to right was that the res.8 table rows =
should be limited to those rows which have a resulttypeid =3D 108. These =
all have numeric values, vis :

select distinct resultvalue from results where resulttypeid  =3D 108 =
order by resultvalue;                                                    =
    =20
 resultvalue=20
-------------
       932.0
       933.0
       934.0
       935.0
       936.0
       937.0
       938.0
       939.0
       940.0
      3224.0
(10 rows)

and it should then be only these rows that are joined to the =
resultlookup table=85 but it seems that the rlu8.id =3D res8.resultvalue =
is being done first.

Can I prevent that? Using a subquery, or a some other approach.

> 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.

That might just be it - the query explain is different for the same =
query on each machine.=20

Just to confuse the issue, if I take the resultlookup table out =
completely, I still get the same error. So maybe it isn't that join at =
all that is raising the error.

If I take the results table out=85 it works(the commented code below =
being the change.)=20

SELECT rep.id, --res.reportid,=20
=
round(st_distance_sphere('0101000020BB1000008716D9CEF7A36240643BDF4F8DA741=
C0', post.the_point)/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 res ON rep.id =3D res.reportid  AND res.resulttypeid =
=3D 108 AND res.del is false
WHERE rep.del IS false AND rep.projectid =3D 51
AND round(st_distance_sphere( =
'0101000020BB1000008716D9CEF7A36240643BDF4F8DA741C0', =
post.the_point)/1000) < 150
AND spe.id =3D 9465;

I'm really not sure what to do here.

cheers

Ben




--=20

Ben Madin

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