postgresqlcross-database

How to update table records in a database based on a table with the same table in another database?

Let's assume that I have db1 and db2, two databases. I would like to perform a command of the like of

update db2.person p2
set p2.name = p1.name
from db1.person p1
where p1.id = p2.id;

This is possible in MySQL without any problems. I have great difficulty achieving it in PostgreSQL.

What I have tried:

create extension postgres_fdw;

create server theservername
foreign data wrapper postgres_fdw
options(host 'localhost', dbname 'thedbname', port '5432');

create user mapping for theuser
server theservername
options(user 'theusername', password 'thepassword');

And here I'm stuck, I don't know how to proceed. None of these troubles exist in MySQL. How can I overcome them in PostgreSQL?

Solution

  • Steps are following:

    Step - 1: Create Extension

    create extension postgres_fdw;

    Step - 2: Create Server

    create server theservername
foreign data wrapper postgres_fdw
options(host 'localhost', dbname 'thedbname', port '5432');

    Step - 3: Create Foreign User Mapping for the server

    create user mapping for theuser
server theservername
options(user 'theusername', password 'thepassword');

    Step - 4: Create Foreign Table with same structure as in another DB

    create foreign table "schema_name"."local_table_name"
(
id_ int;
...
-- field list same as foreign table in other db
)
server theservername
options(SCHEMA_NAME 'foreign_schema', TABLE_NAME 'foreign_name');

    Now you can use local_table_name in your query just as local table. It will do all operations on remote db.

    Your update query can be written like below:

    update local_table_name p2
set name = p1.name
from person p1
where p1.id = p2.id;