Postgres Upsert Cardinality Violation

October 19, 2022

I am trying to insert extracted data from a sql table into a postgres table where the rows may or may not exist. If they do exist, I would like to set a specific column to its default (0)

The table is as

site_notes (
job_id text primary key,
attachment_id text,
complete int default 0);

My query is

INSERT INTO site_notes (
                job_id,
                attachment_id
            )
            VALUES 
                {jobs_sql}
            ON CONFLICT (job_id) DO UPDATE
            SET complete = DEFAULT;

However I am getting an error: psycopg2.errors.CardinalityViolation: 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.

Would anyone be able to advise on how to set the complete column to the default on event of a conflict ?

Many Thanks

>Solution :

An INSERT ... ON CONFLICT DO UPDATE statement (and indeed an UPDATE statement too) is not allowed to modify the same row more than once. It is not clear what {jobs_sql} in your question is, but it must contain several rows, and at least two of those have the same job_id.

Make sure that the same job_id does not occur more than once in the rows you want to INSERT.