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 tbl (id int, my_string text);
INSERT INTO tbl VALUES
  (1, '{ELEPHANT:1, FENNEC_FOX:1NAKED_MOLE:1URCHIN:2}')
, (2, '{DUNG_BEETLE:12URCHIN:1}')
, (3, '{DUNG_BEETLE:1FENNEC_FOX:1URCHIN:2}')
;

/* Desired results:
    {ELEPHANT:1, FENNEC_FOX:1, NAKED_MOLE:1, URCHIN:2}
    {DUNG_BEETLE:1, URCHIN:1}
    {DUNG_BEETLE:1, FENNEC_FOX:1, URCHIN:2}
*/

CREATE TABLE



INSERT 0 3


-- positive lookahead (fastest)
SELECT *, regexp_replace(my_string, '(:\d+)(?=[a-zA-Z])', '\1, ', 'g') AS fixed
FROM   tbl;


id
my_string
fixed


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

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

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



SELECT 3


-- positive lookbehind (slower for this pattern)
SELECT *, regexp_replace(my_string, '(?<=:\d+)([a-zA-Z])', ', \1', 'g') AS fixed
FROM   tbl;


id
my_string
fixed


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

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

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



SELECT 3

id	my_string	fixed
1	{ELEPHANT:1, FENNEC_FOX:1NAKED_MOLE:1URCHIN:2}	{ELEPHANT:1, FENNEC_FOX:1, NAKED_MOLE:1, URCHIN:2}
2	{DUNG_BEETLE:12URCHIN:1}	{DUNG_BEETLE:12, URCHIN:1}
3	{DUNG_BEETLE:1FENNEC_FOX:1URCHIN:2}	{DUNG_BEETLE:1, FENNEC_FOX:1, URCHIN:2}