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

Question

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?

Answer 1

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;