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. NULL/1000 = NULL round(NULL) = NULL AND NULL < 150 = 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@xxxxxxxxxxxxx> wrote: > Ben Madin <ben@xxxxxxxxxxxxx> writes: >> On 2013-02-06, at 13:42 , Tom Lane <tgl@xxxxxxxxxxxxx> 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 = rep.id AND res8.resulttypeid = 108 AND res8.del = false >>>> LEFT JOIN resultlookup rlu8 ON rlu8.resulttypesid = 108 AND rlu8.id = 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. > > 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. > >> So my understanding, working left to right was that the res.8 table rows should be limited to those rows which have a resulttypeid = 108. > > 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 = 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. > >> I'm really not sure what to do here. > > 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... > > regards, tom lane -- Ben Madin t : +61 8 6102 5535 m : +61 448 887 220 e : ben@xxxxxxxxxxxxx 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. -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
