0

I'd like to find the easiest and most elegant way to merge two Postgres databases that have the same schemas.

Assume I have the following two databases, with one table each.

db 1, table A

name    | birthdate  | fav_color
john    | 10-10-2010 | blue
peter   | 12-12-2010 | green
michael | 09-09-2008 | black

db 2, table A

name  | birthdate  | fav_color
john  | 10-10-2015 | yellow
peter | 12-12-2015 | green
carl  | 12-12-2015 | white

I'd like to merge all tables in db2 into db1. If one row already exists in db1, replace it with the one from db2.

db merged, table A

name    | birthdate  | fav_color
john    | 10-10-2015 | yellow
peter   | 12-12-2015 | green
carl    | 12-12-2015 | white
michael | 09-09-2008 | black

Is this something I could do with an UPDATE-FROM-SELECT query, similar to this question? If yes, is there a way to do this for all the tables in the database and without manually specifying all the fields?

Thanks everyone!

Community
  • 1
  • 1
vfxdev
  • 678
  • 6
  • 13
  • possible duplicate of [Insert, on duplicate update in PostgreSQL?](http://stackoverflow.com/questions/1109061/insert-on-duplicate-update-in-postgresql) – Marc B Jul 20 '15 at 14:19
  • It's a Three pipes problem. Between databases, with same schemas, try using dblink to query both databases in one join and make a third table with the results. – Frank N Stein Jul 20 '15 at 18:34

0 Answers0