Postgres 16

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 my_table(my_string varchar(100));
insert into my_table values
('{ELEPHANT:1, FENNEC_FOX:1NAKED_MOLE:1URCHIN:2}'),
('{DUNG_BEETLE:12URCHIN:1}'),
('{DUNG_BEETLE:1FENNEC_FOX:1URCHIN:2}');

CREATE TABLE

INSERT 0 3

SELECT REGEXP_REPLACE(my_string, '(:\d+)(\w)', '\1, \2', 'g')
FROM my_table

regexp_replace

{ELEPHANT:1, FENNEC_FOX:1, NAKED_MOLE:1, URCHIN:2}

{DUNG_BEETLE:12, URCHIN:1}

{DUNG_BEETLE:1, FENNEC_FOX:1, URCHIN:2}

SELECT 3

SELECT REGEXP_REPLACE(REGEXP_REPLACE(my_string, '(:\d+)(\w)', '\1, \2', 'g'), '(\w+):', '"\1":', 'g')
FROM my_table

regexp_replace

{"ELEPHANT":1, "FENNEC_FOX":1, "NAKED_MOLE":1, "URCHIN":2}

{"DUNG_BEETLE":12, "URCHIN":1}

{"DUNG_BEETLE":1, "FENNEC_FOX":1, "URCHIN":2}

SELECT 3