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 tbl (id int, arr text[]);
INSERT INTO tbl VALUES 
     (1, '{a}')
   , (2, '{NULL}')
   , (3, '{}')
   , (4, '{x,y,z}');
   
CREATE TABLE

INSERT 0 4

SELECT id, elem
FROM   tbl t, unnest(
   CASE WHEN array_length(t.arr, 1) >= 1
        THEN t.arr
        ELSE '{null}'::text[] END
   ) elem;

id
elem

1
a

2
null

3
null

4
x

4
y

4
z

SELECT 6

SELECT id, elem
FROM   tbl t
LEFT   JOIN LATERAL unnest(t.arr) elem ON true;

id
elem

1
a

2
null

3
null

4
x

4
y

4
z

SELECT 6

id	elem
1	a
2	null
3	null
4	x
4	y
4	z

id	elem
1	a
2	null
3	null
4	x
4	y
4	z