CODEWITHSUNDEEP
sqlpostgresql-9.1
user1897937
user1897937

Reputation: 419

How do I merge two tables in postgresql?

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?

Upvotes: 36

Views: 88930

Answers (6)

João
João

Reputation: 400

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)

Upvotes: 0

childerino
childerino

Reputation: 401

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.

Upvotes: 13

Alex Dembo
Alex Dembo

Reputation: 350

INSERT ... ON CONFLICT DO NOTHING is much faster than UNION. At least look into the explain statement.

Upvotes: 2

Sandeep
Sandeep

Reputation: 29341

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);

Upvotes: 20

Mariappan Subramanian
Mariappan Subramanian

Reputation: 10063

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);

Upvotes: 1

valex
valex

Reputation: 24144

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
                     )

Upvotes: 58

Related Questions