Depending of this users table structure :
CREATE TABLE users
AS
SELECT id,name,email1,email2
FROM ( VALUES
( 1, 'John', '[email protected]', '[email protected]' ),
( 2, 'Baz' , '[email protected]' , null )
) AS t(id,name,email1,email2);
Is it possible to have this output with one query ?
John | [email protected]
John | [email protected]
Baz | [email protected]
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
CREATE TABLE foo AS SELECT x::int AS x1, trunc(x*3/7)::int AS x2 FROM generate_series(1,1e6) AS t(x); q1: SELECT x1 FROM foo UNION ALL SELECT x2 FROM foo; q2: SELECT x FROM foo CROSS JOIN LATERAL ( VALUES (x1),(x2) ) AS t(x); q2 is twice as slow as q1.
Commented
Jul 6, 2017 at 14:32
UNION) is LATERAL, in my case. Thanks for this addition
SELECT name, email FROM users
UNION
SELECT name, email2 FROM users;
email and email2 have to be of the same type
UNION ALL or it'll be slower and you'll be eliminating duplicates. Like if Bob's email2, is Sally's email1. Or, if two users have the same email address.
Commented
Jul 6, 2017 at 14:30
emailandemail2are provided ?