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
If you change a table’s primary key or add one to a table that didn’t have one, we recommend dropping the existing table in your destination and re-syncing it. For more information, see our documentation on primary key changes.
To re-sync individual tables, do the following:
- In Fivetran, go to your PostgreSQL connector page.
- Select the Schema tab
- Locate and hover over the affected table.
- On the right side of the screen, click Re-sync.
- 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.