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 : [email protected]
--
Sent via pgsql-general mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
