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 yourtable (email varchar(100));

CREATE TABLE


insert into yourtable values ('abc-cde-ghi-abc@test.com -cde@test.com');
insert into yourtable values ('Finance-ddl-sample@yahoo.com-sample1@gmail.com-sample2@msn.com');

INSERT 0 1



INSERT 0 1


WITH separatedMailAddresses AS
  (SELECT 
    email, 
    UNNEST(
      STRING_TO_ARRAY(email, '-')
    ) AS parts 
   FROM yourtable
   GROUP BY email)
SELECT 
  email,
  REPLACE(STRING_AGG(parts, ',')
  FILTER (WHERE parts LIKE '%@%'),' ','') AS email_list
FROM separatedMailAddresses
GROUP BY email


email
email_list


Finance-ddl-sample@yahoo.com-sample1@gmail.com-sample2@msn.com
sample@yahoo.com,sample1@gmail.com,sample2@msn.com

abc-cde-ghi-abc@test.com -cde@test.com
abc@test.com,cde@test.com



SELECT 2

email	email_list
Finance-ddl-sample@yahoo.com-sample1@gmail.com-sample2@msn.com	sample@yahoo.com,sample1@gmail.com,sample2@msn.com
abc-cde-ghi-abc@test.com -cde@test.com	abc@test.com,cde@test.com