SQL Server to PostgreSQL with unique Constraint

Question

I am trying to push data from a MS SQL Server to a PostgreSQL (both on my local device) by using the pg as linked server and calling a stored procedure of the SQL Server.

A simple

INSERT INTO [POSTGRESQL].[mypgdb].[MyPgSchema].[MyPgTable](MyPgCol1, MyPgCol2, MyPgCol3)
(SELECT [MySqlCol1], [MySqlCol2], [MySqlCol3] FROM MySqlTable);

works fine.

The problem is that I need an Unique Constraint on (MyPgCol1, MyPgCol2, MyPgCol3) to avoid duplicate rows of normalized tables and end up with unique constraint violation error when I try to insert the SQL Server data. I know I can insert only new rows by calling

INSERT INTO "MyPgSchema"."MyPgTable"("MyPgCol1", "MyPgCol2", "MyPgCol3")
VALUES (1, 2, 3),
ON CONFLICT ON CONSTRAINT "MyUniqueConstraint_On_Col1_Col2_Col3"
DO NOTHING;

from the Postgre site.

But how can I integrate the ON CONFLICT ON CONSTRAINT... DO NOTHING; in my SQL Server stored procedure? I tried the OPENQUERY statement but the syntaxes do not match.

Is there an other way to call PostgreSQL queries from SQL Server side? Or is it possible to configure the PostgreSQL table to use DO NOTHING on unique constraint violation per default?

Answer 1

One work-around is that you can drop the constraint, and insert the data. And then you can run a SELECT statement to identify the rows. Once these are removed, you can create the index.