How to combine multiple columns into one?

I have the following example table:

| name   | email1          | email2            | email3         | 
.................................................................
| John   | [e-mail]  | [e-mail]  | [e-mail] |
| Jane   | [e-mail]  | NULL              | NULL           |

I need a query to combine the email columns in the way that the result would look like this, ignoring the nulls:

| name   | email            |
.............................
| John   | [e-mail]   |
| John   | [e-mail] | 
| John   | [e-mail]   |
| Jane   | [e-mail]   |

How can I do this?

Answer

You can use a lateral join:

select t.name, v.email
from t cross join lateral
     (values (email1), (email2), (email3)) v(email)
where v.email is not null;