After Adding a Primary Key to a Table, Duplicate Records Remain in the Destination

Issue

After adding a primary key to a table and re-syncing it, duplicate records remain in the destination table.

Environment

All PostgreSQL connectors.

Resolution

We don't hard delete records from your destination. Therefore, you must manually run delete queries to remove duplicated records from your destination.

Alternatively, drop the table from your destination and re-sync it. To re-sync individual tables, do the following:

1. In Fivetran, go to your PostgreSQL connector page.
2. Select the Schema tab
3. Locate and hover over the affected table.
4. On the right side of the screen, click Resync.
5. In the confirmation pop-up window, click Start re-sync.

Cause

If your table doesn't have a primary key, we create a new row when we detect changes. For more information, see our Tables without primary keys documentation. Once you add a primary key to your table, we update the existing row and set applicable _fivetran_id column values to null when we detect changes.

However, we don't hard delete data from your destination. As a result, you may observe duplicate data in your destination from when the table didn't have a primary key.