1

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
3
  • Conditional expressions in SQL? Jul 6 '17 at 12:59
  • @MladenUzelac Sure, but how should I output 2 distinct rows if for one record both email and email2 are provided ?
    – ceadreak
    Jul 6 '17 at 13:01
  • @ceandreak using set operation UNION maybe? Jul 6 '17 at 13:02
2

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

5
  • I think the performance would be substantially detrimental under all workloads. LATERAL isn't exactly fast. Jul 6 '17 at 14:27
  • test: 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. Jul 6 '17 at 14:32
  • 1
    I haven't used LATERAL with Postgresql but for DB2 the number of rows read is usually significantly reduced. In a case like this it probably does not matter at all so I just mentioned it as a side note.
    – Lennart
    Jul 6 '17 at 14:34
  • I could not edit my comment so I'll add a new one. I did a similar test for DB2 as yours, and the time to execute the queries where about the same. The estimated cost for the LATERAL query where about 60% of the UNION query and the number of I/O:s where also reduced to about half.
    – Lennart
    Jul 6 '17 at 14:46
  • I confirm, the fasted (~50% compared to UNION) is LATERAL, in my case. Thanks for this addition
    – ceadreak
    Jul 6 '17 at 15:37
2
SELECT name, email FROM users
UNION
SELECT name, email2 FROM users;

email and email2 have to be of the same type

3
  • So simple, thanks but not elegant if a where clause is added. But it does the job
    – ceadreak
    Jul 6 '17 at 13:12
  • 1
    @ceadreak not really. just wrap the thing in another select. Jul 6 '17 at 14:01
  • Also this should be 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. Jul 6 '17 at 14:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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