I have a specific requirement of extracting data from certain columns of a table(for certain records, identified by a Unique ID) AND update the records with same ID in another table with same values in a column.
Database I am using is Postgresql. I wish if there is a way through extracting using a database tool(DBeaver, PG Admin, etc) with which I can extract from one DB and save values in another DB. I tried extracting values from DBeaver but they were Insert INTO statements, would create duplicate records.
The layman solution I have is to extract in a CSV and then update the records by reading row-by-row through a Rake task.
Any help for suggesting a solution the DB level way is appreciated. Thanks in advance.
Solution:
By using postgres_fdw you can achieve this very easily. Please Follow below mentioned steps on your new database where you want to update the table:
Step - 1: Create Extension
create extension postgres_fdw;
Step - 2: Create Remote Server
create server remote_server
foreign data wrapper postgres_fdw
options(host 'remote_server', dbname 'remote_dbname', port '5432');
Step - 3: Create Foreign User Mapping for the server
create user mapping for localuser
server remote_server
options(user 'remoteuser', password 'remoteuser_password');
Step - 4: Create Foreign Table with same structure as in remote DB
create foreign table "schema_name"."remote_table"
(
id_ int;
...
-- field list same as foreign table in other db
)
server remote_server
options(SCHEMA_NAME 'foreign_schema', TABLE_NAME 'foreign_name');
Now you can use local_table_name in your query just as local table but It will do all operations on remote db.
Your update query can be written like below:
update local_table p1
set
-- your set statements
.....
.....
from remote_table p2
where p1.id = p2.id;
you can drop the remote_table if you feel it is not required in future.
Note: - Above will work only if both databases are on same server or both database can communicate with each other (if on different server).