Postgres 13

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?.

-- error
SELECT to_number('1D4','9D9'); 
ERROR:  numeric field overflow
DETAIL:  A field with precision 1, scale 0 must round to an absolute value less than 10^1.


-- same error
SELECT to_number('14','9D9');
ERROR:  numeric field overflow
DETAIL:  A field with precision 1, scale 0 must round to an absolute value less than 10^1.


-- all the same (no error)
SELECT to_number('1D4' ,'99D9')
     , to_number('14'  ,'99D9')
     , to_number('1DD4','99D9');


to_number
to_number
to_number


14
14
14


-- BUT if length of pattern is exceeded ...
SELECT to_number('1X4'  ,'99.9') AS n1
     , to_number('1XX4' ,'99.9') AS n2
     , to_number('1XXX4','99.9') AS too_much_noise;


n1
n2
too_much_noise


14
14
1


-- 4 leading noise characters consume all 4 pattern characters; error
SELECT to_number('XXXX14','99.9') AS n2;
ERROR:  invalid input syntax for type numeric: " "