How to insert multiple values and handle conflict at the same time

Question

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

Answer 1

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.