Postgres 16

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?.

CREATE TABLE data (id int PRIMARY KEY, age text);

INSERT INTO data VALUES
  (1, '1-5 years')
, (2, '6-10')
, (3, '11')
, (4, '18-21 years')  -- get fraction
;

CREATE TABLE



INSERT 0 4


-- as single expression
SELECT *, CASE
           WHEN age ~ ' ' THEN (split_part(age, '-', 1)::float8 + split_part(split_part(age, ' ', 1), '-', 2)::float8) / 2
           WHEN age ~ '-' THEN (split_part(age, '-', 1)::float8 + split_part(           age         , '-', 2)::float8) / 2
           ELSE age::float8
          END AS avg_age
FROM   data;


id
age
avg_age


1
1-5 years
3

2
6-10
8

3
11
11

4
18-21 years
19.5



SELECT 4


-- with aggregation
SELECT id, age, avg(age_bound) AS avg_age
FROM  (
   SELECT *, unnest(string_to_array(split_part(age, ' ', 1), '-'))::float8 AS age_bound
   FROM   data
   ) sub
GROUP  BY 1, 2;


id
age
avg_age


1
1-5 years
3

3
11
11

2
6-10
8

4
18-21 years
19.5



SELECT 4