By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
select version();
| version |
|---|
| PostgreSQL 13.10 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-16), 64-bit |
SELECT 1
create table planet_osm_point (
place varchar(100),
population varchar(20)
);
create table contr_planet_osm_point (
place varchar(100),
population varchar(20)
);
CREATE TABLE
CREATE TABLE
insert into planet_osm_point values
('town', '100'),('town', '100-200'),('town', '10000'),('town', '38 500'),('town', '38600');
INSERT 0 5
SELECT * FROM planet_osm_point p
| place | population |
|---|---|
| town | 100 |
| town | 100-200 |
| town | 10000 |
| town | 38 500 |
| town | 38600 |
SELECT 5
-- Выбираем по population где только число и больше 4000
SELECT *
FROM planet_osm_point
WHERE place = 'town'
AND population ~ '^[0-9]+$'
AND population::int > 4000
| place | population |
|---|---|
| town | 10000 |
| town | 38600 |
SELECT 2
-- задаем порядок выполнения через подзапрос
SELECT *
FROM (
SELECT *
FROM planet_osm_point
WHERE place = 'town'
AND population ~ '^[0-9]+$'
) t
WHERE population::int > 4000
| place | population |
|---|---|
| town | 10000 |
| town | 38600 |
SELECT 2
-- задаем порядок выполнения через CASE WHEN
SELECT *
FROM planet_osm_point
WHERE place = 'town'
AND CASE WHEN population ~ '^[0-9]+$' THEN population::int > 4000 ELSE false END
;
| place | population |
|---|---|
| town | 10000 |
| town | 38600 |
SELECT 2
-- Отбираем нечисловые значение в population и вставляем в таблицу contr_planet_osm_point
INSERT INTO contr_planet_osm_point
SELECT *
FROM planet_osm_point
WHERE place = 'town'
AND population !~ '^[0-9]+$'
INSERT 0 2
SELECT * FROM contr_planet_osm_point;
| place | population |
|---|---|
| town | 100-200 |
| town | 38 500 |
SELECT 2