0

I have a table where the primary key is a combination of two columns.

When inserting multiple values at a time (batching) conflicts might happen since in the list of values passed there might be duplicates.

I am adding an ON CONFLICT statement to the insert query hoping that the last row being inserted is the final value.

When this query runs, I get the error:

SQL Error [21000]: ERROR: 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.

Below is a contrived example of the table and the query

CREATE TABLE users (
    user text NOT NULL,
    user_email text NOT NULL,
    is_active bool NOT NULL DEFAULT false,
    is_admin bool NOT NULL DEFAULT false,
    PRIMARY KEY (user, user_email)
);

An example query is as follows:

insert into users (user, user_email, is_active, is_admin)
values('user1','user1@example.com','true','false'),('user1','user1@example.com','false','false')
on conflict (user, user_email)
do update set is_active  = excluded.is_active, is_admin  = excluded.is_admin ;

The reason I suspect this error is being throw, correct if I am mistaken, is because the 2 values being inserted both conflict and basically the on conflict statement can't handle them at the same time.

My question, is how to best handle this scenario? how to modify this in such a way to keep batching but on conflict apply that last statement in the list of values(no race condition)

Thanks in advance

1 Answer 1

0

You could use DISTINCT ON () to remove duplicates from the VALUES clause:

insert into users ("user", user_email, is_active, is_admin)
select distinct on ("user", user_email) *
from (
  values 
    ('user1','user1@example.com',true,false),
    ('user1','user1@example.com',false,false)
) as x("user", user_email)  
on conflict ("user", user_email)
do update set is_active  = excluded.is_active, is_admin  = excluded.is_admin;

As no order by is used, distinct on () will pick one of the duplicates randomly.

3
  • Hmm, interesting…is there a way to always select the last one of the distinct values? Basically a group by and then the last one? And in terms of performance, is this something that makes sense to do on the database level or in this case deduplication is better done on the application level?
    – Fouad
    Oct 29, 2022 at 19:59
  • @Fouad: define "last" - if you have something to order by then this possible. If you can't specify a sort order, there is no such thing as the "last" row. Oct 29, 2022 at 20:21
  • Makes total sense…in my case last is the last occurrence of the combination (user, user_email)…i guess in this case it’s easier to group at the application level using a hashmap where the key is the combination of user and user email and periodically flush to the database the deduplicated values unless this can still be done at the database level
    – Fouad
    Oct 30, 2022 at 7:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.