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?

Can't this be done with a simple query that checks for the ID (name is this case) and does a join? — viki.omega9, Dec 28 '12 at 06:19

valex · Answer 1 · 2012-12-28T06:57:50.167

53

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
                     )

edited Dec 28 '12 at 06:57

answered Dec 28 '12 at 06:20

valex

23,966
7
43
60

hi unoin gives the expexted result but table 1 remains same i need to insert the values in table1 whatever new in table2 – user1897937 Dec 28 '12 at 06:50
I've added a query to insert into table1 – valex Dec 28 '12 at 06:58
1

This looks like it doesn't handle deletes so if table 2 didn't have something that table 1 had, it wouldn't delete it, which I would expect from a merge. Maybe I'm looking at that wrong. – swade Jan 30 '19 at 23:18

Sandeep · Answer 2 · 2015-09-07T14:48:20.893

18

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

edited Sep 07 '15 at 14:48

answered Sep 07 '15 at 14:32

Sandeep

28,307
3
32
24

childerino · Answer 3 · 2021-05-06T17:18:33.527

12

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.

edited May 06 '21 at 17:18

answered Apr 21 '21 at 16:22

childerino

331
2
5

2

WITH NO DATA, how long have I not know this, sigh. I always say 'where primary_key_col = 'asdfasdfasdf' or something like that. – nfdavenport Nov 15 '21 at 22:37

score 1 · Answer 4 · answered Dec 28 '12 at 07:24

1

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

answered Dec 28 '12 at 07:24

Mariappan Subramanian

9,527
8
32
33

1

I don't think PostgreSQL supports MERGE INTO, maybe in version 9.5? – Joril Jul 21 '15 at 12:44
1

Nope: https://www.postgresql.org/docs/13/unsupported-features-sql-standard.html See F312 F313 and F314. – Adrien Clerc Dec 21 '20 at 20:53
This is SQL server TSQL syntax. – zhongxiao37 Jun 01 '21 at 03:48
https://www.postgresql.org/message-id/attachment/23520/sql-merge.html – Hugh Jones Jun 29 '22 at 07:36
the merge statement appears to be slated for release in v 15 – Hugh Jones Jun 29 '22 at 11:08
@zhongxiao37 I believe it is an ANSI syntax - https://en.wikipedia.org/wiki/Merge_(SQL) – Hugh Jones Jun 29 '22 at 11:12

score 1 · Answer 5 · answered Mar 06 '21 at 22:56

1

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

answered Mar 06 '21 at 22:56

Alex Dembo

330
4
13

score 0 · Answer 6 · answered Feb 22 '23 at 10:28

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)

How do I merge two tables in postgresql?

6 Answers6