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