Create a new table based on existing other table in postgresql-CodePudding

I have tables like this:

Table_0

CustomerID	Trans_date
C001	01-sep-22
C001	04-sep-22
C001	14-sep-22
C002	03-sep-22
C002	01-sep-22

Table_1

CustomerID	Trans_date
C002	18-sep-22
C002	20-sep-22
C003	02-sep-22
C003	28-sep-22

Table_2

CustomerID	Trans_date
C004	08-sep-22
C004	18-sep-22
C004	20-sep-22
C005	18-sep-22

How to create a new table where the new table consists of table_0, table_1 and table_2 in postgresql? thank you for help

CodePudding user response：

Create a new table, like for exemple 'new_table', then do this command for each of your table: INSERT INTO new_tale SELECT * FROM table_0

CodePudding user response：

This is it:

select a.CustomerID ac customer_id,
a.Trans_date as Trans_date1,
b.Trans_date as Trans_date2, 
c.Trans_date as Trans_date3
where a.CustomerID=b.CustomerID
and b.CustomerID=c.CustomerID;

CodePudding user response：

union example: https://www.postgresqltutorial.com/postgresql-tutorial/postgresql-union/
create table as: https://www.postgresqltutorial.com/postgresql-tutorial/postgresql-create-table-as/

follow the example would be:

CREATE TABLE new_table AS (
    SELECT
        CustomerID,
        Trans_date
    FROM
        Table_0
    UNION ALL
    SELECT
        CustomerID,
        Trans_date
    FROM
        Table_1
    UNION ALL
    SELECT
        CustomerID,
        Trans_date
    FROM
        Table_2)