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 term_size_ref
(term_size text);
INSERT INTO term_size_ref
(term_size)
VALUES ('A0.01'),
('B+100'),
('2.1'),
('S'),
('B0.0001'),
('A0.001'),
('B-7'),
('RH'),
('RH99'),
('.1'),
('-1'),
('+4'),
('2');
13 rows affected
SELECT *
FROM term_size_ref
ORDER BY regexp_replace(term_size,
'[\d.\-+]+$',
'') ASC,
nullif(regexp_replace(term_size,
'^[^\d.\-+]+',
''),
'')::decimal ASC
NULLS FIRST;
| term_size |
|---|
| -1 |
| .1 |
| 2 |
| 2.1 |
| +4 |
| A0.001 |
| A0.01 |
| B-7 |
| B0.0001 |
| B+100 |
| RH |
| RH99 |
| S |