PostgreSQL – Split One Record into Two Rows Based on Two Fields

postgresql

Depending of this users table structure : 

CREATE TABLE users
AS
  SELECT id,name,email1,email2
  FROM ( VALUES 
    ( 1, 'John', 'john@doe.com', 'foo@bar.com' ),
    ( 2, 'Baz' , 'baz@bar.com' , null )
  ) AS t(id,name,email1,email2);

Is it possible to have this output with one query ?

John | john@doe.com
John | foo@bar.com
Baz  | baz@bar.com

Best Answer

In addition to Mladen's answer you can also use LATERAL to transpose columns to rows:

select t.name, t.email
from users u
cross join LATERAL ( values ( u.name, u.email1 )
                          , ( u.name, u.email2 ) ) as t

LATERAL makes it possible to reference tables at the same level that are declared above, so we can create a virtual table with two rows in terms of users.

LATERAL can be beneficial performancewise compared to UNION

Related Question