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?
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
)
We don't need any special MERGE/UPSERT Command.
To merge rows from one table into the other.
INSERT INTO table1
(SELECT * FROM table2
WHERE name NOT IN
(SELECT name FROM table1));
For creating new table from old tables.
CREATE TABLE new_table AS
(SELECT * FROM table1
UNION
SELECT * FROM table2);
Merging tables and "upserting" is such a common db task that it's worth updating this answer for 2021. Assuming the tables are identical, the easiest and fastest way in postgresql:
INSERT INTO table1
SELECT * FROM table2
ON CONFLICT DO NOTHING;
Before populating the upsert values, create 'table2' as an empty copy of 'table1' to ensure all the columns are the same:
CREATE TABLE "table2"
AS TABLE "table1"
WITH NO DATA;
Presto.
Can you check whether this is working 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);
INSERT ... ON CONFLICT DO NOTHING is much faster than UNION. At least look into the explain statement.
If you want to merge vertically two tables obtained from queries with join and/or filters, you should use parenthesis:
(select id, name
from client c
inner join company c2 on c.company_id = c2.id
where c2.country_id = 1)
union
(select id, name
from supplier s
inner join company c on s.company_id = c.id
where c.country_id = 1)
