2

I am replicating from MSSQL (SQL Server 13) to PostgreSQL (9.5) using SymmetricDS.

The table that is replicating has a composite key of 7 different columns. Everything works perfect from an initial load to inserting and updating data. However, I run into a problem whenever I run an update that modifies data in one of the 7 columns that comprise the primary key. On the MSSQL side, it updates the row, no problem. On the Postgres side, rather than updating the column, it inserts an additional row.

If I modify the sym_transform_column entry to have 0 for pk the specific column then it will update the data correctly but will not utilize that column as a primary key to determine which row to update.

Example Generated SQL with pk=0 for sym_transform_column:

update table set pk1 = 0, value1 = 'test', value2 = 'test' where pk2 = 0 and pk3 = 0

Example Generated SQL with pk=1 for sym_transform_column:

update table set value1='test', value2='test' where pk1 = 0 and pk2 = 0 and pk3 = 0

I realize that it is generally accepted that PK should be immutable but to cover all contingencies, is there a way to replicate the update to primary key data from MSSQL to PostgreSQL using SymmetricDS?

Andrew B
  • 21
  • 1

1 Answers1

1

Is it possible to add a column in the source table and treat it as a primary key? It could, for example, be a concatenation if the seven columns that comprise the composite key. Then declare this column as a primary key for the synchronization and add the same column in the table in the target database.

Boris Pavlović
  • 63,078
  • 28
  • 122
  • 148