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
;
CREATE TABLE
INSERT 0 7
-- 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 |
SELECT 7
-- 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 |
SELECT 7
-- 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 |
SELECT 7