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
– freakish, Commented Feb 17, 2021 at 10:39
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
– Frank Heikens, Commented Feb 17, 2021 at 10:41
Yes my problem is that I insert multiple rows with the same function, how can I avoid a duplicate row in this case ? — MHDaouas
– MHDaouas, Commented Feb 17, 2021 at 10:50
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, Commented Feb 17, 2021 at 10:56

Frank Heikens · Accepted Answer · 2021-02-17 13:40:49Z

5

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;

edited Feb 17, 2021 at 13:40

answered Feb 17, 2021 at 10:47

Frank Heikens

129k26 gold badges157 silver badges153 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

MHDaouas Over a year ago

I tried your query but I always get the second date (16 Feb), even when I invert the values and I still don't know how to implement this in Python.

MHDaouas Over a year ago

It looks like the first value is the last one taken.

Frank Heikens Over a year ago

You could add an ORDER BY in the SELECT statement, that's up to you

MHDaouas Over a year ago

Can you please add an ORDER BY date in your example ?

Frank Heikens Over a year ago

@MHDaouas: No problem! Done

|

Collectives™ on Stack Overflow

Cannot update existing row on conflict in PostgreSQL with Psycopg2

1 Answer 1

8 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Related