CODEWITHSUNDEEP
postgresqlupsert
Jaden Walker
Jaden Walker

Reputation: 23

Postgres Upsert Cardinality Violation

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

Upvotes: 2

Views: 1264

Answers (2)

HeyWatchThis
HeyWatchThis

Reputation: 23553

The answer by @Laurenz looks good to me, but I have a more obvious example I think.

I can see how the error doesn't make sense because since you are updating to DEFAULT , logically there would be nothing inconsistent were there to be duplicate job_id in your {jobs_sql} ,

            ON CONFLICT (job_id) DO UPDATE
            SET complete = DEFAULT;

right if you had ('job1', 'pringles'), ('job1', 'postgres'), ('job2', 'prawns') , the interesting bits in attachment_id get thrown out anyway .

Guess that was just a choice made in postgresql's design.

More interesting conflict example

If first

INSERT INTO site_notes (job_id, attachment_id) 
  VALUES ('job1', 'software eng'), ('job3', 'data sci');

then,

INSERT INTO site_notes (job_id, attachment_id) 
  VALUES   
    ('job3', 'couch scientist'), 
    ('job3', 'farm eng'),
    ('job4', 'seismologist')
  ON CONFLICT  (job_id) 
  DO UPDATE 
  SET attachment_id = EXCLUDED.attachment_id
  ;

then here clearly there really is a conflict, should we update job3 with 'couch scientist' or 'seismologist' , we don't know.

Upvotes: 0

Laurenz Albe
Laurenz Albe

Reputation: 247625

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.

Upvotes: 2

Related Questions