Is there a way to UPSERT an Oracle Foreign Table with Foreign Data Wrapper from PostgreSQL?
0
votes
0
answers
189
views
I need to transfer lots of row from my PostgreSQL database to a remote Oracle DB through the Oracle Foreign Data Wrapper (FDW). The preferred way to do it is by UPSERT.
However as INSERT INTO ... ON CONFLICT can not be used for Foreign Table, hence I am using the following that work quite well.
WITH
my_input (
SELECT ...
),
my_update (
UPDATE my_foreign_table f
SET ...
FROM my_input i
WHERE f.id=i.id
RETURNING ...
)
INSERT INTO my_foreign_table f
SELECT ...
FROM my_input
WHERE NOT EXISTS (
SELECT 1 FROM my_foreign_table f
WHERE f.id=a.id )
Now I am curious: Is there a way to do this in a more straight-forward way through Oracle FDW from PostgreSQL other than the above way ? I fear that the above way might not be efficient.
FYI, my playground:
- PostgreSQL 9.6.11 64-bit with PostGIS 2.5.1 on Windows Server 2008 R2 Datacenter
- Oracle DB 11.2.0.4.0 64-bit
Asked by Rino
(229 rep)
Aug 23, 2022, 06:23 AM