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?.
SELECT *
, part[1] AS p1, part[2] AS p2, part[3] AS p3, part[4] AS p4
, part[5] AS p5, part[6] AS p6, part[7] AS p7
FROM (
SELECT test_id, version
, regexp_match(version
, '^(?:(\d+)(\w*)\.?(\d*)(\w*)\.?(\d*)(\w*))?(?:\-*(\w+))?') AS part
FROM (
VALUES
(1, '1.2.3-SNAPSHOT')
, (2, '2-FOO')
, (3, '2')
, (4, '2-BAR')
, (5, '2.1-BAR')
, (6, '13.5.6-SNAPSHOT')
, (7, '13.11.11-SNAPSHOT')
, (8, '13.11a.11-SNAPSHOT')
, (9, '13.11b.11')
, (10, 'Test')
, (11, 'TEST2')
, (12, '1a')
, (13, '1a.1a.1a-foo')
, (14, '1a.1a.1a-')
, (15, '1a.1a.1b-foo')
, (16, 'sp9d8hgf')
, (17, '2a-BAR')
, (18, '2.1a-BAR')
, (19, '2.1ab-BAR')
, (20, 'incorrect1.2-foo')
) x(test_id, version)
) sub
ORDER BY NULLIF(part[1], '')::int NULLS FIRST
, NULLIF(part[2], '') NULLS FIRST
, NULLIF(part[3], '')::int NULLS FIRST
, NULLIF(part[4], '') NULLS FIRST
, NULLIF(part[5], '')::int NULLS FIRST
| test_id | version | part | p1 | p2 | p3 | p4 | p5 | p6 | p7 |
|---|---|---|---|---|---|---|---|---|---|
| 20 | incorrect1.2-foo | {NULL,NULL,NULL,NULL,NULL,NULL,incorrect1} | null | null | null | null | null | null | incorrect1 |
| 16 | sp9d8hgf | {NULL,NULL,NULL,NULL,NULL,NULL,sp9d8hgf} | null | null | null | null | null | null | sp9d8hgf |
| 10 | Test | {NULL,NULL,NULL,NULL,NULL,NULL,Test} | null | null | null | null | null | null | Test |
| 11 | TEST2 | {NULL,NULL,NULL,NULL,NULL,NULL,TEST2} | null | null | null | null | null | null | TEST2 |
| 1 | 1.2.3-SNAPSHOT | {1,"",2,"",3,"",SNAPSHOT} | 1 | 2 | 3 | SNAPSHOT | |||
| 12 | 1a | {1,a,"","","","",NULL} | 1 | a | null | ||||
| 14 | 1a.1a.1a- | {1,a,1,a,1,a,NULL} | 1 | a | 1 | a | 1 | a | null |
| 13 | 1a.1a.1a-foo | {1,a,1,a,1,a,foo} | 1 | a | 1 | a | 1 | a | foo |
| 15 | 1a.1a.1b-foo | {1,a,1,a,1,b,foo} | 1 | a | 1 | a | 1 | b | foo |
| 3 | 2 | {2,"","","","","",NULL} | 2 | null | |||||
| 4 | 2-BAR | {2,"","","","","",BAR} | 2 | BAR | |||||
| 2 | 2-FOO | {2,"","","","","",FOO} | 2 | FOO | |||||
| 5 | 2.1-BAR | {2,"",1,"","","",BAR} | 2 | 1 | BAR | ||||
| 18 | 2.1a-BAR | {2,"",1,a,"","",BAR} | 2 | 1 | a | BAR | |||
| 19 | 2.1ab-BAR | {2,"",1,ab,"","",BAR} | 2 | 1 | ab | BAR | |||
| 17 | 2a-BAR | {2,a,"","","","",BAR} | 2 | a | BAR | ||||
| 6 | 13.5.6-SNAPSHOT | {13,"",5,"",6,"",SNAPSHOT} | 13 | 5 | 6 | SNAPSHOT | |||
| 7 | 13.11.11-SNAPSHOT | {13,"",11,"",11,"",SNAPSHOT} | 13 | 11 | 11 | SNAPSHOT | |||
| 8 | 13.11a.11-SNAPSHOT | {13,"",11,a,11,"",SNAPSHOT} | 13 | 11 | a | 11 | SNAPSHOT | ||
| 9 | 13.11b.11 | {13,"",11,b,11,"",NULL} | 13 | 11 | b | 11 | null |