Re: BUG #10785: error if using x>n AND x<m in where clause, BETWEEN n AND m works as supposed

Yes, you're right. It is my fault. The "population" column contains non-numeric data.
Thanks for reply.
On Fri, Jun 27, 2014 at 20:18, "Tom Lane" wrote:lunda(at)bitmessage(dot)ch (mailto:lunda(at)bitmessage(dot)ch) writes:
DOES NOT WORK (ERROR: invalid input syntax for integer: "tower"):
create index planet_osm_point_city_50k_index on planet_osm_point using
gist(way) where
(((capital IS NULL) OR (capital 'yes'::text)) AND
(place = ANY ('{city,town}'::text[])) AND
(population IS NOT NULL) AND (population ''::text) AND
(regexp_replace(population, '[., ]', '')::int >= 50000) AND
(regexp_replace(population, '[., ]', '')::int < 100000));

You haven't provided nearly enough information for anyone else to
reproduce this problem, but I suppose that the error occurs because
you have some non-numeric entries in "population". You'll need to
code those tests more defensively, perhaps with a CASE that checks
that the string is numeric before trying to cast it.

Probably the "working" examples accidentally fail to fail because
of the order the planner chooses to apply the qual expressions in.
It's not a bug that they're not necessarily done left-to-right; see
http://www.postgresql.org/docs/9.3/static/sql-expressions.html#SYNTAX-EXPRESS-EVAL (http://www.postgresql.org/docs/9.3/static/sql-expressions.html#SYNTAX-EXPRESS-EVAL)

regards, tom lane