Postgres 12

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.

create table versions (v1 varchar(20));

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

insert into versions 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');

11 rows affected

-- this works
SELECT *
FROM versions
WHERE v1 < '2.0'
ORDER BY v1 COLLATE en_natural desc;

v1

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

-- this sort of works, but the ordering is wrong
SELECT *
FROM versions
WHERE v1 < '2.0'
ORDER BY v1 desc;

v1

1.2.9

1.24

1.23.45-rc.2+B001

1.23.231-test.beta

1.23.231-test.alpha

1.23.231+b

1.23.231+a

1.23

10.1

0.9

-- this doesn't work, includes version 10.1
-- this works
SELECT *
FROM versions
WHERE v1 < '2'
ORDER BY v1 desc;

v1

1.2.9

1.24

1.23.45-rc.2+B001

1.23.231-test.beta

1.23.231-test.alpha

1.23.231+b

1.23.231+a

1.23

10.1

0.9