question

BenC-2868 avatar image
0 Votes"
BenC-2868 asked jlmarina-3136 published

Copy to Azure Postgresql with upsert

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?

azure-data-factoryazure-sql-databaseazure-database-postgresql
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

VaibhavChaudhari avatar image
1 Vote"
VaibhavChaudhari answered jlmarina-3136 published

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

· 5
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

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

0 Votes 0 ·

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://docs.microsoft.com/en-us/azure/data-factory/tutorial-copy-data-portal#create-a-pipeline

0 Votes 0 ·

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?

0 Votes 0 ·
Show more comments