Postgres 11

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 alnum ( data text );

INSERT INTO alnum VALUES
 ('')
,(NULL)
,('A')
,('B')
,('0')
,('00')
,('0A')
,('0B')
,('00B')
,('0A0B')
,('0B0B')
,('0B00B')
,('0B000B')
,('0B0B')
,('0B0B0')
,('00B0B00')   -- added special cases

,('AAA1BB')
,('AAA10B')
,('AAA20B')
,('AAA11B')
,('AAA21B')
,('X1C1')
,('X1C3')
,('X1C30')
,('X1C4')
,('X1C10')
,('X2C1')
,('X10C2')
,('X10C10')
,('1A')
,('10A')
,('2A');

CREATE TABLE expected_order	(
  ordinal serial primary key,
  sorteditem text
);

-- Create composite type once per DB
CREATE TYPE ai AS (a text, i int);

CREATE TABLE

INSERT 0 32

CREATE TABLE

CREATE TYPE

SELECT data
FROM   alnum
ORDER  BY ARRAY(SELECT ROW(x[1], CASE x[2] WHEN '' THEN '0' ELSE x[2] END)::ai FROM regexp_matches(data, '(\D*)(\d*)', 'g') x)
        , data;

data

null

0

00

0A

0A0B

00B

0B

00B0B00

0B000B

0B00B

0B0B

0B0B

0B0B0

1A

2A

10A

A

AAA1BB

AAA10B

AAA11B

AAA20B

AAA21B

B

X1C1

X1C3

X1C4

X1C10

X1C30

X2C1

X10C2

X10C10

SELECT 32

SELECT data
FROM  (
    SELECT ctid, data, regexp_matches(data, '(\D*)(\d*)', 'g') AS x
    FROM   alnum
    ) x
GROUP  BY ctid, data   -- ctid as stand-in for a missing pk
ORDER  BY regexp_replace (left(data, 1), '[1-9]', '0')
        , array_agg(ROW(x[1], CASE x[2] WHEN '' THEN '0' ELSE x[2] END)::ai)
        , data         -- for special case of trailing 0

data

0

00

0A

0A0B

00B

0B

00B0B00

0B000B

0B00B

0B0B

0B0B

0B0B0

1A

2A

10A

A

AAA1BB

AAA10B

AAA11B

AAA20B

AAA21B

B

X1C1

X1C3

X1C4

X1C10

X1C30

X2C1

X10C2

X10C10

SELECT 31