CODEWITHSUNDEEP
postgresqlspring-boot
Tisha
Tisha

Reputation: 857

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

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

Upvotes: 0

Views: 6269

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246788

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.

Upvotes: 2

Related Questions