How do I merge two tables in postgresql?

Question

I have two tables

table 1:

name| count xxx  | 1 yyyy | 2 zzzz | 3 

table 2:

name |count xxx  | 1 aaa  | 5 

I want the resulting table to be like the following table:

name | count xxx  | 1 yyyy | 2 zzzz | 3 aaa  | 5 

Does anyone know how to do this?

Answer 1

You should use UNION.

select * from table1 union select * from table2 

To insert into table 1:

INSERT INTO TABLE1 select * from table2      where not exists(             select * from table1                   where name=TABLE2.Name                         and count=TABLE2.Count                      ) 

Answer 2

We don't need any special MERGE/UPSERT Command.

1. To merge rows from one table into the other.

   INSERT INTO table1   (SELECT * FROM table2    WHERE name NOT IN        (SELECT name FROM table1)); 

2. For creating new table from old tables.

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