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 tbl (id int, elements text, arr text[]);
INSERT INTO tbl VALUES
  (1, 'ab,cd,efg', '{ab,cd,efg}')
, (2, 'jk'          , '{jk}')  -- single element
, (3, ''            , '{}')    -- empty string / array
, (4, null          , null)    -- null string / array
;

CREATE TABLE



INSERT 0 4


-- Postgres 14+: preserve all rows from tbl
SELECT t.id, a.elem, a.nr
FROM   tbl AS t
LEFT   JOIN LATERAL string_to_table(t.elements, ',')
                    WITH ORDINALITY AS a(elem, nr) ON true;


id
elem
nr


1
ab
1

1
cd
2

1
efg
3

2
jk
1

3
null
null

4
null
null



SELECT 6


-- Postgres 14+: eliminate rows without elements
SELECT t.id, a.elem, a.nr
FROM   tbl t, string_to_table(t.elements, ',') WITH ORDINALITY a(elem, nr);


id
elem
nr


1
ab
1

1
cd
2

1
efg
3

2
jk
1



SELECT 4


-- Postgres 9.4 - 13: preserving all rows from tbl
SELECT t.id, a.elem, a.nr
FROM   tbl AS t
LEFT   JOIN LATERAL unnest(string_to_array(t.elements, ','))
                    WITH ORDINALITY AS a(elem, nr) ON true;


id
elem
nr


1
ab
1

1
cd
2

1
efg
3

2
jk
1

3
null
null

4
null
null



SELECT 6


-- Postgres 9.4 - 13: eliminate rows without elements
SELECT t.id, a.elem, a.nr
FROM   tbl t, unnest(string_to_array(t.elements, ',')) WITH ORDINALITY a(elem, nr);


id
elem
nr


1
ab
1

1
cd
2

1
efg
3

2
jk
1



SELECT 4


-- Postgres 9.4+: unnest actual array
SELECT t.id, a.elem, a.nr
FROM   tbl t, unnest(t.arr) WITH ORDINALITY a(elem, nr);


id
elem
nr


1
ab
1

1
cd
2

1
efg
3

2
jk
1



SELECT 4


SELECT id, a, ordinality
FROM   tbl, unnest(arr) WITH ORDINALITY a;


id
a
ordinality


1
ab
1

1
cd
2

1
efg
3

2
jk
1



SELECT 4


SELECT id, a.*
FROM   tbl, unnest(arr) WITH ORDINALITY a;


id
a
ordinality


1
ab
1

1
cd
2

1
efg
3

2
jk
1



SELECT 4


SELECT id, a.* FROM tbl, unnest(arr) WITH ORDINALITY a;


id
a
ordinality


1
ab
1

1
cd
2

1
efg
3

2
jk
1



SELECT 4


SELECT * FROM tbl, unnest(arr) WITH ORDINALITY a;


id
elements
arr
a
ordinality


1
ab,cd,efg
{ab,cd,efg}
ab
1

1
ab,cd,efg
{ab,cd,efg}
cd
2

1
ab,cd,efg
{ab,cd,efg}
efg
3

2
jk
{jk}
jk
1



SELECT 4

id	elements	arr	a	ordinality
1	ab,cd,efg	{ab,cd,efg}	ab	1
1	ab,cd,efg	{ab,cd,efg}	cd	2
1	ab,cd,efg	{ab,cd,efg}	efg	3
2	jk	{jk}	jk	1

id	elem	nr
1	ab	1
1	cd	2
1	efg	3
2	jk	1
3	null	null
4	null	null

id	elem	nr
1	ab	1
1	cd	2
1	efg	3
2	jk	1
3	null	null
4	null	null