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

From: lunda(at)bitmessage(dot)ch
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #10785: error if using x>n AND x<m in where clause, BETWEEN n AND m works as supposed
Date: 2014-06-27 13:54:05
Message-ID: 20140627135405.15694.38636@wrigleys.postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

The following bug has been logged on the website:

Bug reference: 10785
Logged by: j.l.
Email address: lunda(at)bitmessage(dot)ch
PostgreSQL version: 9.3.4
Operating system: Linux 64bit (ubuntu 12.04)
Description:

DOES WORK:
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));

DOES WORK:
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 between 50000 and 99999));

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));

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Alvaro Herrera 2014-06-27 15:58:18 Re: Bogus pg_class.relminmxid value for pg_database
Previous Message David G Johnston 2014-06-27 13:33:56 Re: pg import text data to not null table comma error but semicolon right