How to combine two tables in postgresql

Question

• List item

Table 1:

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

table 2:

 name |count xxx | 1 aaa | 5 

I need the result in table 1, for example: table 1:

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

please give your ideas

Answer 1

You must use UNION.

 select * from table1 union select * from table2 

Insert in 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 do not need a special MERGE / UPSERT team.

• Join rows from one table to another.

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

• To create a new table from old tables.

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

Answer 3

You can check if this works in your developer,

 MERGE INTO table1 x USING table2 b ON ( x.name=b.name and x.count=b.count) WHEN NOT MATCHED THEN INSERT (x.name,x.count)VALUES(b.name,b.count);