Cannot update existing row on conflict in PostgreSQL with Psycopg2

Question

I have the following function defined to insert several rows with iteration in Python using Psycopg2 and PostgreSQL 11. When I receive the same obj (with same id), I want to update its date.

def insert_execute_values_iterator(
    connection,
    objs: Iterator[Dict[str, Any]],
    page_size: int = 1000,
) -> None:
    with connection.cursor() as cursor:
        try:
            psycopg2.extras.execute_values(cursor, """
                INSERT INTO objs(\
                                                id,\
                                                date,\
                ) VALUES %s \
                ON CONFLICT (id) \
                    DO UPDATE SET (date) = (EXCLUDED.date) \
            """, ((
                obj['id'],
                obj['date'],

            ) for obj in objs), page_size=page_size)
        except (Exception, Error) as error:
            print("Error while inserting as in database", error)

When a conflict happens on the unique primary key of the table while inserting an element, I get the error:

Error while inserting as in database 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.

FYI, the clause works on PostgreSQL directly but not from the Python code.

"command cannot affect row a second time" isn't the error message clear? You pass duplicates in one query. It is unlikely that this works with postgresql directly. More likely you didn't test it with duplicates. Remove duplicates before passing the data to the database. Postgres doesn't allow multiple conflicts on a single constraint in one query. — freakish
Do you INSERT a single row (value) or multiple? It looks like you have multiple conflicts on the same row, that is not allowed. — Frank Heikens
Yes my problem is that I insert multiple rows with the same function, how can I avoid a duplicate row in this case ? — MHDaouas
Don't use VALUES for your INSERT statement, but a SELECT statement using a DISTINCT ON() to avoid duplicates within your statement — Frank Heikens

Frank Heikens Frank Heikens · Accepted Answer · 2021-02-17T10:47:54

Use unique VALUE-combinations in your INSERT statement:

create table foo(id int primary key, date date);

This should work:

INSERT INTO foo(id, date) 
VALUES(1,'2021-02-17') 
ON CONFLICT(id) 
DO UPDATE SET date = excluded.date;

This one won't:

INSERT INTO foo(id, date) 
VALUES(1,'2021-02-17') , (1, '2021-02-16') -- 2 conflicting rows
ON CONFLICT(id) 
DO UPDATE SET date = excluded.date;

DEMO

You can fix this by using DISTINCT ON() in a SELECT statement:

INSERT INTO foo(id, date) 
SELECT DISTINCT ON(id) id, date
FROM (VALUES(1,CAST('2021-02-17' AS date)) , (1, '2021-02-16')) s(id, date)
ORDER BY id, date ASC
ON CONFLICT(id) 
DO UPDATE SET date = excluded.date;

Cannot update existing row on conflict in PostgreSQL with Psycopg2

1 Answers