1

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

Богдан Иманкулов
  • 50
  • 1
  • 6

1 Answers1

3

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|
Stefanov.sm
  • 11,215
  • 2
  • 21
  • 21
  • 1
    No, it won't solve my problem because i have more than 1000 of voters, and i'm doing it with Java, and i need to speed up my programm, but doing it in the loop will take a loong time. I need something like: 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 count = count +1 but with Postgres – Богдан Иманкулов Aug 15 '21 at 06:13
  • Do the loop in PL/pgSQL not in Java and it will be very fast. I do not think that data management and/or ETL in the application layer is a good idea anyway. You may find some hints [here](https://stackoverflow.com/questions/67716292/postgres-copy-from-program-into-a-dynamic-table-with-unknown-columns/67717875#comment121518558_67717875), though the case is different. – Stefanov.sm Aug 15 '21 at 14:40