CODEWITHSUNDEEP
sqlpostgresql
user2616355
user2616355

Reputation: 463

how to sort a column which contains Semantic Version in Postgres

Is there a way to efficiently store https://semver.org version string in Postgres and then do a latest query . e.g. Say column has values 1.1.0, 1.0.0, 1.2.0, 1.0.1-rc.1+B001 . I would like to sort and get the latest version number (1.2.0) ideally an optimized and performant query as it will be queried frequently.

Note: I would like to sort on a single column and get latest not compare 2 different columns. Also would like it to be full semver compliant.

Upvotes: 8

Views: 3673

Answers (3)

perotom
perotom

Reputation: 913

One could parse the string and sort by each component of the semantic version:

SELECT version FROM software
ORDER BY 
(string_to_array(version, '.'))[1] DESC, 
(string_to_array(version, '.'))[2] DESC, 
(string_to_array((string_to_array(version, '.'))[3], '-'))[1] DESC, 
(string_to_array((string_to_array(version, '.'))[3], '-'))[2] DESC NULLS FIRST

Upvotes: 0

Chris Lawlor
Chris Lawlor

Reputation: 48902

Accepted answer does work for sorting, but will include e.g. version "10.1" if querying for e.g version < '2'. See https://dbfiddle.uk/?rdbms=postgres_12&fiddle=ad031218fe4c941f29c9b03de80d54e0.

If, instead, you define the collation on the column, you get the correct results when filtering by version:

CREATE COLLATION en_natural (
  LOCALE = 'en-US-u-kn-true',
  PROVIDER = 'icu'
);

CREATE TABLE test (
  version varchar(20) collate en_natural
);

insert into test values
('10.1'),
('2.1'),
('1.2.9'),
('1.24'),
('1.23.231+b'),
('1.23.231+a'),
('1.23'),
('1.23.231-test.beta'),
('1.23.231-test.alpha'),

('1.23.45-rc.2+B001'),
('0.9');

SELECT *
FROM test
WHERE version < '2.0'
ORDER BY version desc;

-- note results do not include 10.1
1.24
1.23.231+b
1.23.231+a
1.23.231-test.beta
1.23.231-test.alpha
1.23.45-rc.2+B001
1.23
1.2.9
0.9

https://dbfiddle.uk/?rdbms=postgres_12&fiddle=a284745f2b55617a964c777c29b7e745

Upvotes: 10

Laurenz Albe
Laurenz Albe

Reputation: 246188

Try an ICU collation with natural sorting order:

CREATE COLLATION en_natural (
   LOCALE = 'en-US-u-kn-true',
   PROVIDER = 'icu'
);

Then if you use ORDER BY somecol COLLATE en_natural, you should get what you want.

DB Fiddle

Upvotes: 5

Related Questions