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

select version();

version

PostgreSQL 13.4 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.4.1 20200928 (Red Hat 8.4.1-1), 64-bit

CREATE TABLE payment 
(
  id INT,
  amount TEXT NOT NULL
);
INSERT INTO payment VALUES                          -- Desired result

(1, 'KES 0.80__asdfa   .80..98..00sadf   '),        --  0.80 or .8 or .80
(2, '00 a 0..0...00asafd 0000013..0.85...0000'),    --  0.85   or .85
(3, '0sf00..0...0 00.000 X013....12.851...0000'),   --  12.85
(4, '000..0...007600.00 0013..12.....0000'),        --  7600
(5, '0afs0 sdff 00...00000.56.....000343..343.0'),  --  0.56   or .56
(6, 'fs0 sdff 00...0.000710x00.56..asfd0003..3.0'); --  0.56   or .56 and NOT 0.00071
6 rows affected

SELECT
  REGEXP_REPLACE(amount, '[^0-9\.]+|\. +|\.0|\.{2,}', '', 'g')
FROM payment;

regexp_replace

0.80.809800

00000000000130.850000

00000000001312.8510000

000000760000013120000

000000000.56000343343

00000071000.5600033

--
-- Test ground for StackOverflow question!
--

SELECT
  REGEXP_REPLACE(amount, '_your_regex_pattern_goes_here_', '', 'g')
FROM payment;

--
-- On completion, the regex should be able to feed into the regex below and
-- obtain amounts that CAST to DOUBLE PRECISION
--

regexp_replace

KES 0.80__asdfa   .80..98..00sadf

00 a 0..0...00asafd 0000013..0.85...0000

0sf00..0...0 00.000 X013....12.851...0000

000..0...007600.00 0013..12.....0000

0afs0 sdff 00...00000.56.....000343..343.0

fs0 sdff 00...0.000710x00.56..asfd0003..3.0

SELECT
  NULLIF(NULL, REGEXP_REPLACE(amount, '[^0-9\.]+|\. +|\.0|\.{2,}', '', 'g'))::DOUBLE PRECISION
FROM payment;

nullif

null

null

null

null

null

null

SELECT
  NULLIF(0::TEXT, REGEXP_REPLACE(amount, '[^0-9\.]+|\. +|\.0|\.{2,}', '', 'g'))::DOUBLE PRECISION
FROM payment;

nullif

0

0

0

0

0

0

nullif
null
null
null
null
null
null