How do I eliminate a second seq scan over a table when deriving a new table?
Answer inspired from this

A few methods, all test with PostgreSQL 9.5.

CROSS JOIN LATERAL ... VALUES

This is actually slower, but it seems good for a first attempt..

SELECT id, x
FROM foo
CROSS JOIN LATERAL (VALUES (md5),(trunc::text))
  AS t(x);

                                                     QUERY PLAN                                                      
---------------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=0.00..50982.43 rows=1514052 width=64) (actual time=0.035..1934.061 rows=2000000 loops=1)
   -&gt;  Seq Scan on foo  (cost=0.00..16916.26 rows=757026 width=72) (actual time=0.027..114.655 rows=1000000 loops=1)
   -&gt;  Values Scan on "*VALUES*"  (cost=0.00..0.03 rows=2 width=32) (actual time=0.000..0.001 rows=2 loops=1000000)
 Planning time: 0.115 ms
 Execution time: 2027.840 ms
(5 rows)


CROSS JOIN ... VALUES CASE

SELECT id, CASE WHEN x THEN md5 ELSE trunc::text END AS x 
FROM foo
CROSS JOIN (VALUES (true),(false))
  AS t(x);

                                                     QUERY PLAN                                                      
---------------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=0.00..43412.20 rows=1514052 width=73) (actual time=0.036..1318.494 rows=2000000 loops=1)
   -&gt;  Seq Scan on foo  (cost=0.00..16916.26 rows=757026 width=72) (actual time=0.026..108.375 rows=1000000 loops=1)
   -&gt;  Materialize  (cost=0.00..0.04 rows=2 width=1) (actual time=0.000..0.000 rows=2 loops=1000000)
         -&gt;  Values Scan on "*VALUES*"  (cost=0.00..0.03 rows=2 width=1) (actual time=0.002..0.003 rows=2 loops=1)
 Planning time: 0.104 ms
 Execution time: 1381.685 ms
(6 rows)


Row duplication with ARRAY/unnest

SELECT id, x
FROM foo
CROSS JOIN LATERAL unnest(ARRAY[md5,trunc::text])
  AS t(x);

                                                      QUERY PLAN                                                      
----------------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=0.01..1530968.27 rows=75702600 width=64) (actual time=0.036..3329.324 rows=2000000 loops=1)
   -&gt;  Seq Scan on foo  (cost=0.00..16916.26 rows=757026 width=72) (actual time=0.015..156.087 rows=1000000 loops=1)
   -&gt;  Function Scan on unnest t  (cost=0.01..1.01 rows=100 width=32) (actual time=0.002..0.003 rows=2 loops=1000000)
 Planning time: 0.054 ms
 Execution time: 3439.064 ms
(5 rows)


tldr;

Neither of these methods are faster. They're slower, and more complex. Stick with the UNION ALL.