3

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

Cyberguille
  • 1,482
  • 1
  • 29
  • 53
  • 1
    Have a look at: https://stackoverflow.com/questions/22953450/postgres-create-table-from-select – McNets Jun 15 '17 at 20:34
  • 1
    @McNets thanks a lot I only missing to put as you answer this I going to a accept, basically was useful this answer for me https://stackoverflow.com/a/22953921/2399444 – Cyberguille Jun 15 '17 at 20:41
  • @McNets In my case notice that I don't want to have duplicate in my new table, I have a column id – Cyberguille Jun 15 '17 at 20:47
  • If possible add your table schema and some sample data – McNets Jun 15 '17 at 20:49

1 Answers1

4

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

a_horse_with_no_name
  • 497,550
  • 91
  • 775
  • 843