Need to export table into 2 columns, one repeating, one with rest stacked

Question

Have a table that has one column I want to duplicate, corresponding to several columns I want to stack on top of each other. I have a table like this (foo):

|col1   |col2   |col3   |
|   1   |   A   |   F   |
|   2   |   B   |   G   |
|   3   |   C   |   H   |
|   4   |   D   |   I   |
|   5   |   E   |   J   |

And I want to create a table like this (foo2):

|col_a  |col_b  |
|   1   |   A   |
|   2   |   B   |
|   3   |   C   |
|   4   |   D   |
|   5   |   E   |
|   1   |   F   |
|   2   |   G   |
|   3   |   H   |
|   4   |   I   |
|   5   |   J   |

This is as far as I got, this only stacks into one column:

DROP TABLE IF EXISTS foo2 CASCADE;
select col_a INTO foo2 from (
select col1 as col_a from foo 
union all 
select col2 as col_a from foo
union all 
select col3 as col_a from foo
) as myview;

Answer 1

select col_a, col_b INTO foo2 from (
       select col1 as col_a, col2 as col_b from chandlerV2
       union all 
       select col1 as col_a, col3 as col_B from chandlerV2
     ) as myview;

Each part of subquery myview select main column (col1) and one of two secondary column (col2 or col3 respectively). Both part united by UNION ALL clause.

As variant you can CREATE TABLE and after that run INSERT INTO ... SELECT ...

Answer 2

You're close. You want UNION ALL

SELECT col1, col2 FROM foo
UNION ALL
SELECT col1, col3 FROM foo;

This runs two queries and stacks them on top of each other -- returning all the rows in both queries.

Now use CREATE TABLE AS

CREATE TABLE foo2
AS
  SELECT col1 AS cola, col2 AS colb FROM foo
  UNION ALL
  SELECT col1, col3 FROM foo;

Things to note,

• We use an AS in the first select list to set the column names. We didn't have to though, we could wrap the SELECT ... UNION ALL in another SELECT that sets the column names.
• We use CREATE TABLE AS rather than archaic SELECT INTO