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.
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
WHERE version < '2.0' while this one DOES, is because Laurenz doesn't use en_natural in the definition of the version column. Although he uses en_natural in SELECT ... ORDER BY version COLLATE en_natural, that only applies to the ORDER BY, not the WHERE. Thus Laurenz' WHERE uses the default collation to evaluate version < '2.0', not en_natural. Using en_natural in column definition as Chris does here avoids that mismatch and eliminates the need to specify ORDER BY ... COLLATE en_natural.
- should be less than (for example, a beta) and the non-prerelease without the - should be greater than (for example, the stable) runkit.com/embed/kk7mvcduuip3Try 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.
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
1.23.45-rc.1+B001complete semver specs