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

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

regards, tom lane