Postgres 11

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 tab
AS
SELECT 'ab.cde.fghi' AS c
UNION ALL
SELECT 'ab.cde'
UNION ALL
SELECT 'ab'
3 rows affected

SELECT c, sub.arr[array_upper(arr, 1)-1]
FROM tab
,LATERAL (SELECT string_to_array(tab.c, '.'))sub(arr)


c
arr


ab.cde.fghi
cde

ab.cde
ab

ab
null


SELECT c, (string_to_array(tab.c, '.'))[array_upper((string_to_array(tab.c, '.')), 1)-1]
FROM tab


c
string_to_array


ab.cde.fghi
cde

ab.cde
ab

ab
null