I want to copy from data lake to azure database postgresql using upsert option, is that possible? I don't want to truncate or remove records, to insert or update if they exist. If not, what are my other options?

One option that can be tried is - To copy the data from ADL to Postgresql staging table Have Lookup activity - Use query that will perform Upsert from staging table to main table Please don't forget to Accept Answer and Up-vote if the response helped -- Vaibhav

Copy to Azure Postgresql with upsert

Accepted answer

Vaibhav Chaudhari 38,631 Reputation points

2020-10-20T08:29:33.31+00:00

One option that can be tried is - To copy the data from ADL to Postgresql staging table

Have Lookup activity - Use query that will perform Upsert from staging table to main table

Please don't forget to Accept Answer and Up-vote if the response helped -- Vaibhav
Please sign in to rate this answer.

1 person found this answer helpful.
Ben C 166 Reputation points

2020-10-20T09:26:34.29+00:00

@Vaibhav Chaudhari thanks, I think that might work, what command does ADF use to copy data to postgresql? it uses the COPY command?

Vaibhav Chaudhari 38,631 Reputation points

2020-10-20T09:43:29.133+00:00

ADF uses Copy Activity to copy the data. This activity has to be dragged and dropped on the pipeline and Source and Sink has to be configured.

For reference - https://learn.microsoft.com/en-us/azure/data-factory/tutorial-copy-data-portal#create-a-pipeline

Ben C 166 Reputation points

2020-10-20T10:20:27.65+00:00

thanks, I run using lookup with query:
INSERT INTO affiliations SELECT * FROM affiliations_import ON CONFLICT (id) DO UPDATE SET mag_id = EXCLUDED.id;

this works and values are updated in DB but the pipeline throws error on Lookup Activity, how to make it succeeded?

Navtej Singh Saini 4,221 Reputation points Microsoft Employee

2020-10-27T22:24:41.27+00:00

@Ben C
Please convey if you were able to resolve this or you need further assistance.

Thanks
Navtej S

jlmarina 1 Reputation point

2020-12-02T10:37:51.633+00:00

Lookup needs some output.
You can add "SELECT 1 MYVALUE;" to your lookup query.
Sign in to comment

Copy to Azure Postgresql with upsert

0 additional answers