I need someone to explain why a cardinality violation happens?

I have a table with emails and names, and the email column is unique. I followed someone’s post on here that explained how to do a copy_from when you are dealing with a unique column. The process is A) create a temp table based on the table you are inserting the data, B) copy in the data from the file into the temp table, and C) copy the temp table to the main table with an ON CONFLICT statement to deal with uniques. See code below. For some reason, this is resulting in the 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.

Is this happening because there are two identical emails in the file I’m copying from? If so, how else can I achieve this (copy in a file using copy_from and merge fields when there’s a duplicate email)?

The code:

with connection.cursor() as stmt: stmt.execute('CREATE TEMP TABLE tmp_main AS SELECT * FROM main WHERE 0 = 1') with open(file) as f: stmt.copy_from(f, 'tmp_main', columns=('email','name'), sep=',') stmt.execute(f'INSERT INTO main (email,name) SELECT email,name FROM tmp_main ON CONFLICT (email) DO UPDATE SET name = coalesce(excluded.name,main.name)') #ERROR OCCURS AT THIS LINE stmt.execute('DROP TABLE tmp_main') 

submitted by /u/maxiedaniels
[link] [comments]


Go to Source of this post
Author Of this post: /u/maxiedaniels
Title Of post: I need someone to explain why a cardinality violation happens?
Author Link: {authorlink}