Postgresql : Merge 2 similar databases

Question

I have a requirement of merging two databases (Database1 & Database2) to a common database in Postgresql.

Database1

Table1

Id - Value (Id is Primary key)

1 - a
2 - aa
3 - aaa
4 - aaaa

Database2

Table1

Id Value (Id is Primary key)

2 - bb
5 - bbbbb

I want my output as

OutPutDatabase

Table1

Id Value (Id is Primary key)

1 - a
2 - bb
3 - aaa
4 - aaaa
5 - bbbbb

How can I achieve this?

Answer 1

First, load the tables into two separate schemas in the same database.

CREATE SCHEMA db1;
CREATE TABLE db1.table1 (id INT PRIMARY KEY, value VARCHAR(10));
-- load the contents of Table1 @ Database1 into db1.table1

CREATE SCHEMA db2;
CREATE TABLE db2.table1 (id INT PRIMARY KEY, value VARCHAR(10));
-- load the contents of Table1 @ Database2 into db2.table1

Then you can merge the two, by prioritizing db2 over db1.

SELECT
  DISTINCT ON (id)
  id,
  value
FROM (
  SELECT
    *,
    1 AS db
  FROM
    db1.table1

  UNION

  SELECT
    *,
    2 AS db
  FROM
    db2.table1) AS mix
ORDER BY
  id,
  db DESC;