Postgres 13

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, csv text);
INSERT INTO tbl VALUES
  (1, 'a1, b1, c1')
, (2, 'a2, b2, c2, d2, e2, f2')
, (3, 'a3, b3, c3, d3, e3, f3, g3, h3, i3, j3')
, (4, 'a4')
, (5, 'a5, b5, c5')
, (6, '')    -- empty
, (7, NULL)  -- null
;
7 rows affected

-- simple solution for small number of elements
SELECT id, 1 AS rnk
     , split_part(csv, ', ', 1) AS c1
     , split_part(csv, ', ', 2) AS c2
     , split_part(csv, ', ', 3) AS c3
     , split_part(csv, ', ', 4) AS c4
     , split_part(csv, ', ', 5) AS c5
FROM   tbl
WHERE  split_part(csv, ', ', 1) <> '' -- skip empty rows

UNION ALL
SELECT id, 2
     , split_part(csv, ', ', 6)
     , split_part(csv, ', ', 7)
     , split_part(csv, ', ', 8)
     , split_part(csv, ', ', 9)
     , split_part(csv, ', ', 10)
FROM   tbl
WHERE  split_part(csv, ', ', 6) <> '' -- skip empty rows

-- more?

ORDER  BY id, rnk;


id
rnk
c1
c2
c3
c4
c5


1
1
a1
b1
c1



2
1
a2
b2
c2
d2
e2

2
2
f2





3
1
a3
b3
c3
d3
e3

3
2
f3
g3
h3
i3
j3

4
1
a4





5
1
a5
b5
c5




-- for any number of elements
SELECT t.id, c.rnk
     , split_part(c.csv5, ', ', 1) AS c1
     , split_part(c.csv5, ', ', 2) AS c2
     , split_part(c.csv5, ', ', 3) AS c3
     , split_part(c.csv5, ', ', 4) AS c4
     , split_part(c.csv5, ', ', 5) AS c5
FROM   tbl t
     , unnest(string_to_array(regexp_replace(csv, '((?:.*?,){4}.*?),', '\1;', 'g'), '; ')) WITH ORDINALITY c(csv5, rnk)
ORDER  BY t.id, c.rnk


id
rnk
c1
c2
c3
c4
c5


1
1
a1
b1
c1



2
1
a2
b2
c2
d2
e2

2
2
f2





3
1
a3
b3
c3
d3
e3

3
2
f3
g3
h3
i3
j3

4
1
a4





5
1
a5
b5
c5




-- or to fill element sfro right to left
SELECT t.id, c.rnk
     , split_part(c.csv5, ', ', 5) AS c1
     , split_part(c.csv5, ', ', 4) AS c2
     , split_part(c.csv5, ', ', 3) AS c3
     , split_part(c.csv5, ', ', 2) AS c4
     , split_part(c.csv5, ', ', 1) AS c5
FROM   tbl t
     , unnest(string_to_array(regexp_replace(csv, '((?:.*?,){4}.*?),', '\1;', 'g'), '; ')) WITH ORDINALITY c(csv5, rnk)
ORDER  BY t.id, c.rnk


id
rnk
c1
c2
c3
c4
c5


1
1


c1
b1
a1

2
1
e2
d2
c2
b2
a2

2
2




f2

3
1
e3
d3
c3
b3
a3

3
2
j3
i3
h3
g3
f3

4
1




a4

5
1


c5
b5
a5

id	rnk	c1	c2	c3	c4	c5
1	1	a1	b1	c1
2	1	a2	b2	c2	d2	e2
2	2	f2
3	1	a3	b3	c3	d3	e3
3	2	f3	g3	h3	i3	j3
4	1	a4
5	1	a5	b5	c5

id	rnk	c1	c2	c3	c4	c5
1	1	a1	b1	c1
2	1	a2	b2	c2	d2	e2
2	2	f2
3	1	a3	b3	c3	d3	e3
3	2	f3	g3	h3	i3	j3
4	1	a4
5	1	a5	b5	c5

id	rnk	c1	c2	c3	c4	c5
1	1			c1	b1	a1
2	1	e2	d2	c2	b2	a2
2	2					f2
3	1	e3	d3	c3	b3	a3
3	2	j3	i3	h3	g3	f3
4	1					a4
5	1			c5	b5	a5

id	rnk	c1	c2	c3	c4	c5
1	1	a1	b1	c1
2	1	a2	b2	c2	d2	e2
2	2	f2
3	1	a3	b3	c3	d3	e3
3	2	f3	g3	h3	i3	j3
4	1	a4
5	1	a5	b5	c5

id	rnk	c1	c2	c3	c4	c5
1	1	a1	b1	c1
2	1	a2	b2	c2	d2	e2
2	2	f2
3	1	a3	b3	c3	d3	e3
3	2	f3	g3	h3	i3	j3
4	1	a4
5	1	a5	b5	c5

id	rnk	c1	c2	c3	c4	c5
1	1			c1	b1	a1
2	1	e2	d2	c2	b2	a2
2	2					f2
3	1	e3	d3	c3	b3	a3
3	2	j3	i3	h3	g3	f3
4	1					a4
5	1			c5	b5	a5

id	rnk	c1	c2	c3	c4	c5
1	1	a1	b1	c1
2	1	a2	b2	c2	d2	e2
2	2	f2
3	1	a3	b3	c3	d3	e3
3	2	f3	g3	h3	i3	j3
4	1	a4
5	1	a5	b5	c5

id	rnk	c1	c2	c3	c4	c5
1	1	a1	b1	c1
2	1	a2	b2	c2	d2	e2
2	2	f2
3	1	a3	b3	c3	d3	e3
3	2	f3	g3	h3	i3	j3
4	1	a4
5	1	a5	b5	c5

id	rnk	c1	c2	c3	c4	c5
1	1			c1	b1	a1
2	1	e2	d2	c2	b2	a2
2	2					f2
3	1	e3	d3	c3	b3	a3
3	2	j3	i3	h3	g3	f3
4	1					a4
5	1			c5	b5	a5