CODEWITHSUNDEEP
sqlpostgresqlversion-sort
bodacious
bodacious

Reputation: 6695

Is it possible to order by substrings in postgreSQL?

I have a table called widgets that has a string column named version.

Data for version is in period-separated string format, similar to semantic versioning. e.g. "1.2.4"

When I do the traditional ORDER BY widgets.version then I get the following order

+--------------+
|    Widgets   |
+----+---------+
| id | version |
|----|---------|
| 1  | 1.3.2   |  <- This is fine
| 3  | 10.1.2  |  <- This should be last, since 10 > 4
| 2  | 4.5.7   |  <- This should be second, since 4 < 10
+----+---------+

How can I update my query so that the order returned is by version pt 1, version pt 2, then version pt 3?

Upvotes: 2

Views: 279

Answers (3)

joop
joop

Reputation: 4503

Ugly hack: cast to a network address: (only works for small values, short strings, etc)

SELECT * FROM widgets
ORDER BY version::cidr
        ;

Upvotes: -1

gmaliar
gmaliar

Reputation: 5479

Have you tried?

SELECT
  id,
  split_part(version, '.', 1)::int as major,
  split_part(version, '.', 2)::int as minor,
  split_part(version, '.', 3)::int as patch
FROM
  widgets
ORDER BY major, minor, patch

Upvotes: 1

user330315
user330315

Reputation:

The easiest way is to convert the version string to an integer array and then sort on that array:

select id, 
       version
from widgets
order by string_to_array(version, '.')::int[]

Note that this will fail if the version contains non-numeric values.

Upvotes: 8

Related Questions