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

Question

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

Have a look at: https://stackoverflow.com/questions/22953450/postgres-create-table-from-select — McNets, Jun 15 '17 at 20:34
@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

score 4 · Accepted Answer · 2017-06-15T21:20:18.410

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

edited Jun 15 '17 at 21:20

answered Jun 15 '17 at 20:55

I think that the order of the select is important – Cyberguille Jun 15 '17 at 21:19
1

@Cyberguille: see my update, you need to specify the columns explicitly. Another good example on why `select *` should be avoided – Jun 15 '17 at 21:20
But I have to remove to the new table the duplicate, is a minimum detail that I can solved using this answer https://stackoverflow.com/a/12963112/2399444, but is possible solve this in this query – Cyberguille Jun 15 '17 at 21:31
1

@Cyberguille: `UNION` **will** remove duplicates. – Jun 15 '17 at 21:32
Let me clarify something I don't have pkey or unique id in this tables, and I don't want to have duplicates id, and I can get two rows with equal id and different field1 – Cyberguille Jun 15 '17 at 22:07

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

1 Answers1