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 | |
|---|---|
| 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 | |
|---|---|
| 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 | |
|---|---|
| 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