Postgres 12

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 12.17 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-20), 64-bit

SELECT 1

CREATE TABLE stores
(
  store_id INTEGER NOT NULL,
  emails TEXT NOT NULL
);

CREATE TABLE

INSERT INTO stores
VALUES
(1, 'user_1@example.com, user2@example.com'),
(2, 'uswe3@example.com, user4@example.com'),
(4, 'user_1@example.com,user2@example.com');

INSERT 0 3

SELECT 
  store_id,
  UNNEST(STRING_TO_ARRAY(emails, ',')) AS email
FROM stores;

store_id
email

1
user_1@example.com

1
 user2@example.com

2
uswe3@example.com

2
 user4@example.com

4
user_1@example.com

4
user2@example.com

SELECT 6

--
-- Note records with store_ids 1 & 2
--
-- The 2nd emails have  a preceding space due to the choice of 
-- delimiter as ',' - you can experiment to see what happens 
-- if you use ', ' (comma space) as the delimiter
--

SELECT 
  store_id,
  '*_' || UNNEST(STRING_TO_ARRAY(emails, ',')) || '_*' AS email
FROM stores;

store_id
email

1
*_user_1@example.com_*

1
*_ user2@example.com_*

2
*_uswe3@example.com_*

2
*_ user4@example.com_*

4
*_user_1@example.com_*

4
*_user2@example.com_*

SELECT 6

--
-- And with the TRIM() function, the preceding (and trailing if applicable)
-- spaces are removed and the dummy '*_' (or '_*') is tight against the email
--

SELECT 
  store_id,
  CONCAT('*_', TRIM(UNNEST(STRING_TO_ARRAY(emails, ','))), '_*') AS email
FROM stores;

store_id
email

1
*_user_1@example.com_*

1
*_user2@example.com_*

2
*_uswe3@example.com_*

2
*_user4@example.com_*

4
*_user_1@example.com_*

4
*_user2@example.com_*

SELECT 6

SELECT 
  store_id,
  STRING_TO_ARRAY(emails, ',') AS email_array
FROM stores;

store_id
email_array

1
{user_1@example.com," user2@example.com"}

2
{uswe3@example.com," user4@example.com"}

4
{user_1@example.com,user2@example.com}

SELECT 3

store_id	email
1	user_1@example.com
1	user2@example.com
2	uswe3@example.com
2	user4@example.com
4	user_1@example.com
4	user2@example.com

store_id	email
1	_user_1@example.com_
1	_ user2@example.com_
2	_uswe3@example.com_
2	_ user4@example.com_
4	_user_1@example.com_
4	_user2@example.com_

store_id	email_array
1	{user_1@example.com," user2@example.com"}
2	{uswe3@example.com," user4@example.com"}
4	{user_1@example.com,user2@example.com}