5

I have the following table, named stores:

store_id INT
emails VARCHAR

That contains values:

And I want to generate the following set:

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.

1 Answer 1

8

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.