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

Thank you to all for your help on this problem. I've summarised the =
resolution in the hope that it might help someone else.

With all the advice I have gone forward and discovered that the issue =
related to a postcode anomaly. A client had provided a new postbox =
postcode (the application normally prevents this for postboxes because =
we can't locate properties, but because it was new - and our database =
didn't have a record of it - this check had been bypassed). This meant =
there was no geometry associated with the postcode, and when it was =
joined to the postcodes table (which has varchars for postcodes because =
in Australia some postcodes begin with 0, which needs to be printed to =
allow automatic sorting) during the distance checking function (which =
looked like this in pl/pgsql):

round(st_distance_sphere( '$$ || pccentre || $$', post.the_point)/1000)

If a geometry is NULL, the st_distance_sphere postgis function returned =
NULL.=20

NULL/1000 =3D NULL

round(NULL) =3D NULL

AND NULL < 150 =3D NULL

so the predicate probably looks like:

AND round(NULL/1000) < 150

AND NULL, so no row returned.

This can't be used in a comparison, so to get around this (thanks Tom) :

coalesce(round(st_distance_sphere( '$$ || pccentre || $$', =
post.the_point)/1000),0) < $$ || quote_literal(distance);

which works - problem no longer being seen.

My final throught relates to the message:

    ERROR:  invalid input syntax for integer: ''

The '' suggests (I don't think I was the only one who thought this) that =
we were looking for a string comparison. I guess the NULL value is in =
there between the quotes.

cheers

Ben








On 2013-02-07, at 00:01 , Tom Lane <tgl@sss.pgh.pa.us> wrote:

> Ben Madin <ben@ausvet.com.au> writes:
>> 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:
>=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
>>>                                                             =
^^^^^^^^^^^^^^^^^^^^^
>>> 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.
>=20
>> 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.
>=20
> NULLs are not the problem (casting a NULL to anything is still a =
NULL).
> The problem you've got is with empty strings, which are not at all the
> same thing, even if Oracle can't tell the difference.
>=20
>> 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.
>=20
> Please recall the section in the fine manual where it points out that
> WHERE clauses are not evaluated left-to-right.  In the case at hand
> I think the planner may be able to rearrange the join order, such that
> the rlu8 join is done first.  Now, having said that, I'm not real sure
> why the res8.resulttypeid =3D 108 clause couldn't be applied at scan =
level
> not join level.  But you really need to be looking at EXPLAIN output
> rather than theorizing about what order the clauses will be checked =
in.
>=20
>> I'm really not sure what to do here.
>=20
> You need to make sure the join clause is safe to evaluate for any data
> present in the table.  The first question I'd ask is why isn't
> resultvalue of a numeric type to start with --- this whole problem
> smells of crummy schema design.  Or at least, why can't you use NULL
> for the offending values instead of empty strings.  If you really =
can't
> fix the data representation, you need to complicate the join clause to
> make it not try to convert non-integral strings to ints.  One possible
> solution is "nullif(res8.resultvalue, '')::int", if empty strings are
> the only hazard.  If they're not, you could do something with a CASE
> expression using a regex test on the string...
>=20
>             regards, tom lane


--=20

Ben Madin

t : +61 8 6102 5535
m : +61 448 887 220
e : ben@ausvet.com.au

AusVet Animal Health Services
P.O. Box 5467
Broome   WA   6725
Australia

AusVet's website:  http://www.ausvet.com.au

This transmission is for the intended addressee only and is confidential =
information. If you have received this transmission in error, please =
delete it and notify the sender. The contents of this email are the =
opinion of the writer only and are not endorsed by AusVet Animal Health =
Services unless expressly stated otherwise. Although AusVet uses virus =
scanning software we do not accept liability for viruses or similar in =
any attachments. Thanks for reading.