Fixing the “ON CONFLICT DO UPDATE command cannot affect row a second time” Error in job to Postgres

When running a Catchr job to load data into PostgreSQL, you may encounter this error:

“SQLSTATE[21000]: Cardinality violation: 7 ERROR: ON CONFLICT DO UPDATE command cannot affect row a second time"

HINT: Ensure that no rows proposed for insertion within the same command have duplicate constrained values.”

This error occurs when PostgreSQL receives multiple rows in a single batch that share the same unique key. Because of this, the ON CONFLICT rule cannot determine which row should be inserted or updated, multiple rows try to affect the same target row, causing a cardinality violation.

Why does this happen with Catchr?

This typically occurs when the dataset coming from your connector includes breakdown fields (e.g., campaign ID, ad set ID, date, country, etc.) but the Catchr job is not configured with the correct partition fields.

If the partition fields do not uniquely identify each row, Catchr may send multiple rows with the same primary key values. PostgreSQL then detects duplicates within the same batch and raises the error.

How to Prevent the Error

To avoid duplicated keys and ensure PostgreSQL can correctly apply the ON CONFLICT logic, you must:

Use the IDs of all breakdowns selected in the query as partition fields.

During the job creation process in Catchr:

1. Select your data source and configure your query (metrics + breakdowns).

2. In the field selection step, identify all the breakdowns you added (for example: campaign_id, adset_id, date, etc.).

3. In the partition fields configuration, add the ID fields corresponding to these breakdowns.

4. Continue the setup and rerun the job.

This ensures each row in your dataset is uniquely identified, preventing duplicates within the same batch.

Example

If your query uses the breakdowns:

• Date

• Campaign

• Ad set

Then you should use these partition fields:

• date

• campaign_id

• adset_id

This combination makes every row unique for PostgreSQL and avoids the cardinality violation.

Summary

The error means PostgreSQL received duplicate rows with identical unique keys in the same batch.
To fix it: include all breakdown IDs as partition fields when creating your Catchr job.

If the issue persists even after setting the correct partition fields, contact Catchr support with the job ID and example rows.