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, you can simply:

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


One can cast a whole text array to an integer array (to sort 9 before 10).
One can ORDER BY array types. This is the same as ordering by each of the elements. And shorter arrays come before longer ones with identical leading part.

db&lt;>fiddle here
Old SQL Fiddle.