Split a comma-separated record into its own rows

Question

I have the following table, named stores:

store_id INT
emails VARCHAR

That contains values:

store_id	emails
1	[email protected],[email protected]
2	[email protected],[email protected]
4	[email protected]

And I want to generate the following set:

store_id	emails
1	[email protected]
1	[email protected]
2	[email protected]
2	[email protected]
4	[email protected]

As you can see I want to split the emails field into a separate record containing only one email address. Do you have any idea how I can do that?

So far I managed to create the following query:

select store_id,string_to_array(emails,',') from stores

But I don't know how I can split the string_to_array to its own row.

Answer 1

You need to use UNNEST() in conjunction with STRING_TO_ARRAY() as follows (fiddle available here):

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

Populate:

INSERT INTO stores
VALUES
(1, '[email protected], [email protected]'),
(2, '[email protected], us[email protected]'),
(4, '[email protected],[email protected]');

And then run this query:

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

Result:

store_id    email
1   [email protected]
1    [email protected]
2   [email protected]
2    [email protected]
4   [email protected]
4   [email protected]

One word of caution - depending on how your emails are formatted in the string, you might want to use the TRIM() function to make sure that there are no preceding or trailing spaces in the emails (shown in fiddle).

You can dissect what's happening if you simply run:

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

Result (see updated fiddle) - the emails are still just one record - UNNEST puts each separate element of the array into a different row!:

store_id    email_array
       1    {[email protected]," [email protected]"}
       2    {[email protected]," [email protected]"}
       4    {[email protected],[email protected]}