evgeni evgeni - 2 months ago 17
SQL Question

PostgreSQL: Merge two tables with same column names, add counters

Hi and thank you for reading.

I have two tables with the same columns, the first column is the name and the second is a count. I would like to merge these tables, so that each name appears with the added count of the two tables:

Table1: Table2: Result Table:
NAME COUNT NAME COUNT NAME COUNT
name1 1 name3 3 name1 1
name2 2 name4 4 name2 2
name3 3 name5 5 name3 6
name4 4 name6 6 name4 8
name5 5
name6 6


As of the moment I have created a pretty ugly structure to execute this, and would like to know if it is possible to get the results in a more elegant way.

Thanks you for your advise.

What I have so far (Table1 is test1 and Table2 is test2):

create table test1 ( name varchar(40), count integer);
create table test2 ( name varchar(40), count integer);
create table test3 ( name varchar(40), count integer);
create table test4 ( name varchar(40), count integer);
create table test5 ( name varchar(40), count integer);

insert into test4 (name, count) select * from test1;
insert into test4 (name, count) select * from test2;
insert into test3 (name , count) select t1.name, t1.count + t2.count
from test1 t1 inner join test2 t2 on t1.name = t2.name;
select merge_db(name, count) from test3;
insert into test5 (name, count) (select name, max(count) from test4 group by name);


CREATE FUNCTION merge_db(key varchar(40), data integer) RETURNS VOID AS
$$ -- souce: http://stackoverflow.com/questions/1109061/insert-on-duplicate-update-postgresql
BEGIN
LOOP
-- first try to update the key
UPDATE test4 SET count = data WHERE name = key;
IF found THEN
RETURN;
END IF;-- not there, so try to insert the key -- if someone else inserts the same key concurrently, -- we could get a unique-key failure
BEGIN
INSERT INTO test4(name,count) VALUES (key, data);
RETURN;
EXCEPTION WHEN unique_violation THEN-- do nothing, and loop to try the UPDATE again
END;
END LOOP;
END;
$$
LANGUAGE plpgsql;

Answer
=> create table t1 (name text,cnt int);
=> create table t2 (name text,cnt int);
=> insert into t1 values  ('name1',1), ('name2',2), ('name3',3), ('name4',4);
=> insert into t2 values  ('name3',3), ('name4',4), ('name5',5), ('name6',6);
=> 

select name,sum(cnt) from 
(select * from t1 
union all 
select * from t2 ) X 
group by name 
order by 1;

 name  | sum 
-------+-----
 name1 |   1
 name2 |   2
 name3 |   6
 name4 |   8
 name5 |   5
 name6 |   6
(6 rows)