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 table1 (number1 int, number2 int, min_length int, max_length int);
INSERT INTO table1 VALUES 
  (40, 1801, 8,    8)
, (40, 182 , 8,    8)
, (42, 32  , 6,    8)
, (42, 4,    6,    6)
, (43, 691 , 9,    9)

, (44, NULL, 9,    9)     -- number1 NULL
, (NULL, NULL, 9,    9);  -- number2 NULL
7 rows affected

SELECT t.number1::text || rpad(t.number2::text, len, '0') AS start
     , t.number1::text || rpad(t.number2::text, len, '9') AS stop
FROM   table1 t, generate_series(min_length, max_length) len


start
stop


4018010000
4018019999

4018200000
4018299999

42320000
42329999

423200000
423299999

4232000000
4232999999

42400000
42499999

43691000000
43691999999

null
null

null
null


SELECT COALESCE(t.number1::text, '') || rpad(COALESCE(t.number2::text,''), len, '0') AS start
     , COALESCE(t.number1::text, '') || rpad(COALESCE(t.number2::text,''), len, '9') AS stop
FROM table1 t, generate_series(min_length, max_length) len;


start
stop


4018010000
4018019999

4018200000
4018299999

42320000
42329999

423200000
423299999

4232000000
4232999999

42400000
42499999

43691000000
43691999999

44000000000
44999999999

000000000
999999999

start	stop
4018010000	4018019999
4018200000	4018299999
42320000	42329999
423200000	423299999
4232000000	4232999999
42400000	42499999
43691000000	43691999999
null	null
null	null