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 version
, substring(version, '^(\d+)')::int AS major
, substring(version, '^\d+\.(\d+)')::int AS minor
, substring(version, '^\d+\.\d+\.(\d+)')::int AS incremental
, substring(version, '-(.+)$') AS qualifier
FROM (
VALUES
('1.2.3-SNAPSHOT')
, ('2-FOO')
, ('2-BAR')
, ('2.1-BAR')
, ('13.5.6-SNAPSHOT')
, ('13.11.11-SNAPSHOT')
) x(version)
ORDER BY major NULLS LAST
, minor NULLS FIRST
, incremental NULLS FIRST
, qualifier NULLS FIRST;
| version | major | minor | incremental | qualifier |
|---|---|---|---|---|
| 1.2.3-SNAPSHOT | 1 | 2 | 3 | SNAPSHOT |
| 2-BAR | 2 | null | null | BAR |
| 2-FOO | 2 | null | null | FOO |
| 2.1-BAR | 2 | 1 | null | BAR |
| 13.5.6-SNAPSHOT | 13 | 5 | 6 | SNAPSHOT |
| 13.11.11-SNAPSHOT | 13 | 11 | 11 | SNAPSHOT |