Как получить таблицу с верными данными и с исключениями?

Question

[k79x]

Есть запрос:

SELECT * FROM planet_osm_point WHERE place = 'town' AND population::int > 4000

Проблема заключается в том, что population не всегда integer. Встречаются значения "38 500" и "100-200". Подскажите, как достать все записи, пропуская неподходящие, а спорные (где значение - строка) занести в другую таблицу?

Answer 1

[Сергей c0re]

Используйте регулярное выражение для отбора числовых значений в population
population ~ '^[0-9]+$'

-- Выбираем по population где только число и больше 4000
SELECT *
  FROM planet_osm_point
  WHERE place = 'town'
    AND population ~ '^[0-9]+$'
    AND population::int > 4000

-- Отбираем нечисловые значение в population и вставляем в таблицу contr_planet_osm_point
INSERT INTO contr_planet_osm_point
SELECT *
  FROM planet_osm_point
  WHERE place = 'town'
    AND population !~ '^[0-9]+$'

см. пример на dbfiddle

UPDATE:
по комментариям, для того чтобы "железно" по порядку работало - отбор чисел-> отбор по условию.

SELECT *
  FROM (
    SELECT *
      FROM planet_osm_point
      WHERE place = 'town'
        AND population ~ '^[0-9]+$'
  ) t
  WHERE population::int > 4000

UPDATE2:
да согласен, с xukapy, БД может в принципе как угодно выполнить условия в процессе оптимизации, поэтому лучше жестко задавать порядок выполнения.

считаю можно как подзапросом выше, так и как пишут в статье - PostgreSQL Antipatterns: вычисление условий в SQL
использовать CASE -

WHERE CASE WHEN x > 0 THEN y/x > 1.5 ELSE false END;

SELECT *
  FROM planet_osm_point
  WHERE place = 'town'
    AND CASE WHEN population ~ '^[0-9]+$' THEN population::int > 4000 ELSE false END
;

Comments

[xukapy]

SELECT *
  FROM planet_osm_point
  WHERE population ~ '^[0-9]+$'
    AND population::int > 4000

Вы уверены, что условие population ~ '^[0-9]+$' будет всегда выполняться раньше чем population::int > 4000 ?

[Сергей c0re]

xukapy, не уверен, но это работает по порядку, можете посмотреть пример на dbfiddle, ссылка указана.
И... кто мешает развить тему или надо все полностью - с ложечки? Можно же отбор чисел и подзапросом сделать!?

SELECT *
  FROM (
    SELECT *
      FROM planet_osm_point
      WHERE population ~ '^[0-9]+$'
  ) t
  WHERE population::int > 4000

[Сергей c0re]

xukapy, ну да согласен, БД может в принципе как угодно выполнить условия в процессе оптимизации, поэтому лучше жестко задавать порядок выполнения.

считаю можно как подзапросом выше, так и как пишут в статье - PostgreSQL Antipatterns: вычисление условий в SQL
использовать CASE -

WHERE CASE WHEN x > 0 THEN y/x > 1.5 ELSE false END;

SELECT *
  FROM planet_osm_point
  WHERE place = 'town'
    AND CASE WHEN population ~ '^[0-9]+$' THEN population::int > 4000 ELSE false END
;

[Сергей c0re]

надеюсь теперь ответ удовлетворяет!?

Answer 2

[Дмитрий]

SELECT * FROM planet_osm_point WHERE place = 'town' AND population ~ '^[\d]+$' AND population::int > 4000;

SELECT * FROM planet_osm_point WHERE population !~ '^[\d]+$';