How to ORDER BY typical software release versions like X.Y.Z?

Given a "SoftwareReleases" table:

| id | version |
|  1 | 0.9     |
|  2 | 1.0     |
|  3 | 0.9.1   |
|  4 | 1.1     |
|  5 | 0.9.9   |
|  6 | 0.9.10  |

How do I produce this output?

| id | version |
|  1 | 0.9     |
|  3 | 0.9.1   |
|  5 | 0.9.9   |
|  6 | 0.9.10  |
|  2 | 1.0     |
|  4 | 1.1     |

To produce your desired output:

SELECT id, version
FROM   versions
ORDER  BY string_to_array(version, '.')::int[];

Cast the whole text array to an integer array (to sort 9 before 10) and ORDER BY that.

This is the same as ordering by each of the elements.

Shorter arrays come before longer ones with an equivalent leading part.

db<>fiddle here

Old sqlfiddle

StackExchange Database Administrators Q#74283, answer score: 38