Postgres 12

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 mytable (values text);
INSERT INTO mytable VALUES
('30-20/0.5'),
('20/0.55'),
('30/0.5'),
('30-20/0.5'),
('30/0.55');

CREATE TABLE



INSERT 0 5


SELECT
    (regexp_split_to_array(values, '[-/]'))::decimal[] as a
FROM
    mytable


a


{30,20,0.5}

{20,0.55}

{30,0.5}

{30,20,0.5}

{30,0.55}



SELECT 5


SELECT
    a[1] as col1,
    a[2] as col2,
    COALESCE(a[3], 0) as col3
FROM (
    SELECT
        (regexp_split_to_array(values, '[-/]'))::decimal[] as a
    FROM
        mytable
) s


col1
col2
col3


30
20
0.5

20
0.55
0

30
0.5
0

30
20
0.5

30
0.55
0



SELECT 5