3

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;
2
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 ...

  • 2
    Please don't use select ... into new_table. It's recommended to use the standard create table ... as ... instead nowadays – a_horse_with_no_name Jan 31 '17 at 6:18
1

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,

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.