Ordering dot-delimited numeric sequences (e.g., version numbers)

Question

In my database I've column fill this data:

1
1.1
1.1.1
1.1.1.1
1.1.2
1.10
1.11
1.2
1.9

I want to sort it, to get result looks like this:

1
1.1
1.1.1
1.1.1.1
1.1.2
1.2
1.9    
1.10
1.11

How can I accomplish this? Simple use "ORDER BY" gives wrong result because it's lexicographic order.

Answer 1

You could split the string to an array, cast it to an int[] and rely on Postgres' natural ordering for arrays:

SELECT   mycolumn
FROM     mytable
ORDER BY STRING_TO_ARRAY(mycolumn, '.')::int[] ASC

Answer 2

Casting to cidr will do the trick (if the numbers are well-behaved)

DROP table meuk;
CREATE table meuk
        ( id SERIAL NOT NULL PRIMARY KEY
        , version text
        );


INSERT INTO meuk(version) VALUES
 ('1' )
, ('1.1' )
, ('1.1.1' )
, ('1.1.1.1' )
, ('1.1.2' )
, ('1.10' )
, ('1.11' )
, ('1.2' )
, ('1.9' )
        ;

SELECT * FROM meuk
ORDER BY version::cidr
        ;

---

Result:

INSERT 0 9
 id | version 
----+---------
  1 | 1
  2 | 1.1
  3 | 1.1.1
  4 | 1.1.1.1
  5 | 1.1.2
  8 | 1.2
  9 | 1.9
  6 | 1.10
  7 | 1.11
(9 rows)