PostgreSQL – How to Split a Comma-Separated Record into Rows

postgresqlstring-splitting

I have the following table, named stores:

store_id INT
emails VARCHAR

That contains values:

store_id emails
1 user_1@example.com,user2@example.com
2 uswe3@example.com,user4@example.com
4 admin@example.com

And I want to generate the following set:

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

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.

Best Answer

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, 'user_1@example.com, user2@example.com'),
(2, 'uswe3@example.com, user4@example.com'),
(4, 'user_1@example.com,user2@example.com');

And then run this query:

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

Result:

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

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    {user_1@example.com," user2@example.com"}
       2    {uswe3@example.com," user4@example.com"}
       4    {user_1@example.com,user2@example.com}
Related Question