Search code examples
postgresqlunioncreate-table

Create a new table from Union two tables with union in postgres

I would like to create a new table as the result of the union of two tables without duplicates. I searched in stackoverflow and I found a question with exactly what I want but using mysql Create a new table from merging two tables with union.

Solution in mysql

CREATE TABLE new_table
  SELECT * FROM table1
    UNION
  SELECT * FROM table2;

I tried to do something similar but I got:

SQL error.

I would like to achieve this if is possible with an statement similar to mysql.

I know that if you create a new table first with the fields that I want. I can do a select into this table over the union of this tables. If there aren't other option well I have to do something like this.

But in summary If possible to do something similar to the question with mysql in postgres. I would like to use syntactic sugar to do that

Thanks in advance

Update

In order to clarify I have two table with equal structure 

TABLE1(id,field1,field2,field3)
TABLE2(id,field1,field2,field3)

and The table that I want

TABLE3(id,field1,field2,field3)

Notice that I tried 

CREATE TABLE new_table as
  SELECT * FROM table1
    UNION
  SELECT * FROM table2;

and it works but didn't put the fields in the correct place for example put field3 of table 1 in field 1 of table_result

Solution

  • You are missing the AS keyword:

    CREATE TABLE new_table
AS
  SELECT * FROM table1
    UNION
  SELECT * FROM table2;

    If you need the columns in a specific order, then specify them in the select:

    CREATE TABLE new_table
AS
SELECT id, column1, column2, column3
FROM table1
UNION
SELECT id, column1, column2, column3
FROM table2;

    More details in the manual:
    https://www.postgresql.org/docs/current/static/sql-createtableas.html