ON CONFLICT DO UPDATE cannot affect row a second time, what can i do?

user_16104419 0
Viewed 788

Look, i have this table:

CREATE TABLE voter_count
(id SERIAL, name VARCHAR NOT NULL, birthDate DATE NOT NULL, count INT NOT NULL, PRIMARY KEY(id), UNIQUE (name, birthDate))

I need to add to it several values, and there will be a duplicate of unique key, and when it sees that duplicate it must increase count on existing one, and delete duplicate.

I tried this query:

INSERT INTO voter_count(name, birthdate, count) 
VALUES 
('Ivan', '1998-08-05', 1), 
('Sergey', '1998-08-29', 1), 
('Ivan', '1998-08-05', 1) 
ON CONFLICT (name, birthdate) DO UPDATE SET count = (voter_count.count + 1)

but it throws this error:

on conflict do update cannot affect row a second time.

What can i do to fix this problem?

I need something like this:

INSERT INTO voter_count(name, birthdate, count) 
VALUES 
('Ivan', '1998-08-05', 1), 
('Sergey', '1998-08-29', 1), 
('Ivan', '1998-08-05', 1) 
ON DUPLICATE KEY UPDATE count = count + 1

but with postgres

postgresql-13 postgresql sql
1 Answers

Postgres says "Hint: Ensure that no rows proposed for insertion within the same command have duplicate constrained values". Note the words "same command". So split the insert command. You may do the inserts in a loop.
BTW isn't SET count = (voter_count.count + excluded.count) more correct?
An illustration:

INSERT INTO voter_count(name, birthdate, count) 
VALUES 
('Ivan', '1998-08-05', 1)
ON CONFLICT (name, birthdate) DO UPDATE SET count = (voter_count.count + 1);

INSERT INTO voter_count(name, birthdate, count) 
VALUES 
('Sergey', '1998-08-29', 1)
ON CONFLICT (name, birthdate) DO UPDATE SET count = (voter_count.count + 1);

INSERT INTO voter_count(name, birthdate, count) 
VALUES 
('Ivan', '1998-08-05', 1)
ON CONFLICT (name, birthdate) DO UPDATE SET count = (voter_count.count + 1);

Result:

id|name  |birthdate |count|
--+------+----------+-----+
 7|Sergey|1998-08-29|    1|
 8|Ivan  |1998-08-05|    2|
user_23020320
Related