0

I have two databases with similar data but what I want to do is to update the changes of sample_table from Database2 to sample_table of Database1.

Example:

Database1 [sample_table]

ID     Reference      Name
0001   Ref0001        Test1
0002   Ref0002        Test2

Database2 [sample_table] - with changes

ID     Reference      Name
0001   Ref0001        Name1
0002   Ref0002        Name2

In the sample_table of Database2, I changed the data in the Name field and I want to apply the changes in the sample_table in Database1. What SQL query to execute to do this? I'm using pgAdminIII and pgAdmin4. (both databases are on the same server)

If you have any questions, comment below.

7Eleven
  • 1
  • 6
  • Use fully qualified table names and write a normal update statement? – dfundako Jun 26 '18 at 15:46
  • Postgres does not support cross database queries (or updates). You will need a [foreign table](https://www.postgresql.org/docs/current/static/sql-createforeigntable.html) for that. –  Jun 26 '18 at 15:56
  • @a_horse_with_no_name I've successfully done the process for PostgreSQL's Foreign Data Wrapper but the problem is I don't know the query to replace the **Name** from Database2 to **Name** Database1. – 7Eleven Jun 27 '18 at 07:27

1 Answers1

0

There is no single answer for that question, you could get it solved in so many ways. Let's enumerate a few:

  • Create a plain sql dump for the Database2, edit the table names to be different from the Database1 tables. Execute the modified sql dump into Database1 to have all the data into the same Database. Execute an update from the imported tables into the original ones joining/matching the tuples by id.

  • Like suggested: Create a foreign data wrapper of type postgres_fdw as described to use it to update the original tables from the external datasource directly in postgres.

  • Create a simple external script that connect to boths databases to read from one to update into another one.

  • Use a kind of (ETL, or Replication) software system to be able to keep databases updated. Something like Bucardo or Kettle(Pentaho Data Integration) could do the trick.

All depends on how complex or simple solution do you need, one or few usages or long term usage

aekis.dev
  • 2,626
  • 1
  • 12
  • 19
  • Another option: maybe those tables should be located in two **schemas** rather than two databases. –  Jun 27 '18 at 05:29
  • I was actually trying the postgres_fdw and follow this [postgres_fdw](https://techtipsshare.blogspot.com/2017/08/cross-database-query-in-postgresql.html?showComment=1530071067025#c887574779389436435) but Im getting error in Step #4. – 7Eleven Jun 27 '18 at 05:35
  • How can I do the suggestion #1? Is there a link for me to follow since I'm not actually familiar with it. – 7Eleven Jun 27 '18 at 05:42
  • For #1 you could use this [link](http://www.postgresqltutorial.com/postgresql-update-join/) – aekis.dev Jun 27 '18 at 06:46
  • I've successfully done the dblink and postgres_fdw, my problem now is the query to use to update the **Name** field from DB2 to DB1. – 7Eleven Jun 27 '18 at 07:51