Sample Header Ad - 728x90

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