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?.
WITH conds(cond) AS (
SELECT '[3, 5)'::int4range
UNION ALL
SELECT '[6, 8)'::int4range
UNION ALL
SELECT '[9, 20)'::int4range
)
SELECT int4range(min(r.val), max(r.val) + 1), flag
FROM (SELECT gs.val, (c.cond IS NULL) as flag,
ROW_NUMBER() OVER (PARTITION BY c.cond IS NULL ORDER BY gs.val) as seqnum
FROM (VALUES ('[1, 11)'::int4range)) v(range) CROSS JOIN
generate_series(lower(v.range), upper(v.range), 1) gs(val) LEFT JOIN
conds c
ON gs.val <@ c.cond
) r
GROUP BY flag, r.val - seqnum
ORDER BY min(r.val)
| int4range | flag |
|---|---|
| [1,3) | t |
| [3,5) | f |
| [5,6) | t |
| [6,8) | f |
| [8,9) | t |
| [9,12) | f |