Duplicate key value violates unique constraint in postgres even though key does not exist?

Question

The query that I am using is as follows -

INSERT into mytable(id, tp, booleanOne, classification, booleantwo, created_at) 
        SELECT distinct uttv.id, true, false, uttv.type, false, uttv.created_at 
             FROM userTable tt WHERE booleantwo = false 
                AND type is not null 
                AND tt.id in ( 
      SELECT id from userTable WHERE status=12 AND  booleantwo=false AND booleanThree=false UNION SELECT id FROM userTable WHERE status=19
                   ) 
             AND not exists (select 1 from mytable rre where rre.id = tt.id)

The table has is as its primary key - mytable.id I keep seeing the following error when I run this query -

Key (id)=(2556) already exists.; nested exception is org.postgresql.util.PSQLException: ERROR: duplicate key value violates unique constraint "mytable_modified_pkey

And when I check mytable, I do not see any id that matches 2556.

This query is running as a part of a spring boot project and I have the isolation level set as Seriablizable.

I thought having a UNION in the query would ensure there are no duplicate ids and then using distinct in the outer SELECT query will further ensure we only insert non-duplicates. I know this error is happening because select query is probably returning some duplicates but even then, you will see that I am using a - "AND NOT EXISTS" and ensuring that if an id is already present, do not insert.

Any inputs on why am I seeing this error will be really appreciated. This query is also quite slow.

I also tried executing just the SELECT part of the query by grouping by id and checking if any of the ids occur more than once but I do not see any records for that. And now I can see there are duplicates.

SELECT tags.tag_id, count(1) from (SELECT distinct uttv.id, true, false, uttv.type, false, uttv.created_at 
                 FROM userTable tt WHERE booleantwo = false 
                    AND type is not null 
                    AND tt.id in ( 
          SELECT id from userTable WHERE status=12 AND  booleantwo=false AND booleanThree=false UNION SELECT id FROM userTable WHERE status=19
                       ) as tags group by tags.tag_id having count(1) > 1

I don't think that's a duplicate. There is no WHERE clause to the INSERTstatement. — Laurenz Albe
– Laurenz Albe, Commented Oct 3, 2016 at 14:12

Laurenz Albe · Accepted Answer · 2016-10-03 13:56:31Z

2

I'd say that the SELECT returns more than one row with the same value for the column on which there is a unique constraint on employee.

answered Oct 3, 2016 at 13:56

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Tisha Over a year ago

That's true, but if I try distinct over all the mentions of id in the query, why doesn't that solve the issue?

Laurenz Albe Over a year ago

That depends on the exact SQL statement and the tables you select from... Try to debug the SELECT statement first.

Collectives™ on Stack Overflow

Duplicate key value violates unique constraint in postgres even though key does not exist?

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related