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
(
  amount TEXT NOT NULL
);
INSERT INTO payment VALUES
('KES 0.80'),             
('KES  0.80'),            
('KES   0.80'),           
('xyzKES 0.80'),          
('KES . 0.80'),           
('KES 0.80afasf'),          
('KES 0.80__asdfa'),
('KES 0.80..asdfasdf'),
('KES 0.8.0..asdfasdf'),
('KES 0.8.0..asdfasdf');
10 rows affected

SELECT 
  SUBSTRING(amount FROM '([0-9\.]+)*')
FROM payment;

substring

null

null

null

null

null

null

null

null

null

null

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

regexp_replace

0.80

0.80

0.80

0.80

.0.80

0.80

0.80

0.80..

0.8.0..

0.8.0..

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

btrim

0.80

0.80

0.80

0.80

0.80

0.80

0.80

0.80

0.8.0

0.8.0

SELECT 
  REVERSE(BTRIM(REGEXP_REPLACE(amount, '[^0-9\.]+', '', 'g'), '.'))
FROM payment;

reverse

08.0

08.0

08.0

08.0

08.0

08.0

08.0

08.0

0.8.0

0.8.0

SELECT
ARRAY_LENGTH
(
  STRING_TO_ARRAY
  (
    REVERSE(BTRIM(REGEXP_REPLACE(amount, '[^0-9\.]+', '', 'g'), '.'))
  , '.'
  )
, 1
)
FROM payment;

array_length

2

2

2

2

2

2

2

2

3

3

SELECT
  CASE
    WHEN
ARRAY_LENGTH
(
  STRING_TO_ARRAY
  (
    REVERSE(BTRIM(REGEXP_REPLACE(amount, '[^0-9\.]+', '', 'g'), '.'))
  , '.'
  )
, 1
) > 2 THEN '55555'      -- <<<--- '55555' is just a placeholder!
    ELSE  
    
   REVERSE(ARRAY_TO_STRING(  STRING_TO_ARRAY
  (
    REVERSE(BTRIM(REGEXP_REPLACE(amount, '[^0-9\.]+', '', 'g'), '.'))
  , '.'
  ), '.' ))
    
  END AS str
FROM payment;

str

0.80

0.80

0.80

0.80

0.80

0.80

0.80

0.80

55555

55555

SELECT
  CASE
    WHEN
      ARRAY_LENGTH
      (
        STRING_TO_ARRAY
        (
          REVERSE(BTRIM(REGEXP_REPLACE(amount, '[^0-9\.]+', '', 'g'), '.'))
        , '.'
        )
      , 1
      ) > 2 THEN 

    REVERSE(REGEXP_REPLACE(
    REVERSE(BTRIM(REGEXP_REPLACE(amount, '[^0-9\.]+', '', 'g'), '.')), '\.', ''))

    ELSE  
    
      BTRIM(REGEXP_REPLACE(amount, '[^0-9\.]+', '', 'g'), '.')

  END AS str
FROM payment;

str

0.80

0.80

0.80

0.80

0.80

0.80

0.80

0.80

0.80

0.80

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

regexp_replace

0.8

0.8

0.8

0.8

0.8

0.8

0.8

0.8

0.8

0.8

SELECT
  CASE
    WHEN
      ARRAY_LENGTH
      (
        STRING_TO_ARRAY
        (
          REVERSE(BTRIM(REGEXP_REPLACE(amount, '[^0-9\.]+', '', 'g'), '.'))
        , '.'
        )
      , 1
      ) > 2 
    THEN 
      REVERSE
      (
        REGEXP_REPLACE
        (
          REVERSE
          (
            BTRIM
            (
              REGEXP_REPLACE(amount, '[^0-9\.]+', '', 'g'), '.')
            ), 
        '\.', ''
        )
      )::DOUBLE PRECISION
    ELSE  
      BTRIM(REGEXP_REPLACE(amount, '[^0-9\.]+', '', 'g'), '.')::DOUBLE PRECISION
  END AS str
FROM payment;

str

0.8

0.8

0.8

0.8

0.8

0.8

0.8

0.8

0.8

0.8
substring
null
null
null
null
null
null
null
null
null
null