I have a table with a unique constraint over 5 columns, all nullable=false. These columns are varchar(30), varchar, 3 float8 columns. There is an autoincrement id column, but that is not included in the constraint.
Often times I have duplicates and so my query has "...on conflict do nothing". I have seen a batch insert of rows I that I to know have all duplicates, and so I expect to have 0 affected rows. Instead I have some dupes inserted (From what i have seen it's about 5-15%, it's not constant).
After this, I queried the table with a select distinct across only all columns in the constraint, and postgres will returned the duplicate rows with duplicate values. Same goes for a select query with GROUP BY on all columns in the constraint. I expect the dupe rows to collapse into one, but instead I see both duplicate rows returned to me.
However, when I manually delete one of the two duplicate rows, and then try to insert the single duplicate row then postgresql will rightly tell me there is a conflict and i wont be able to insert.
Am I missing something? My code is now adjusted to insert rows one by one, but of course that is a lot less efficient.
i think it is a bug in postgres actually. I am using 11.6
A friend of mine suggested it might be a corrupted index. i created a cloned table with the same unique index, and i did
insert into ectrades2 select * from ectrades;
this still allowed the dupes to be entered so that's likely not the case. I then tried the same thing but wrote a short program to make individual inserts, and there is a rowcount difference so at least some rows are being filtered, but i cannot confirm all of them are at the moment. i'd have to write another program to do so. the table has almost a billion rows