Error: 'duplicate key value violates unique constraint' even when checking with 'where not in'

Question

I get the error: 'duplicate key value violates unique constraint' even when checking if the primary key is already in the table in which I want to insert.

I have tried to check with either 'where not y.a in...' as well as with 'where not exists...'

The code I use right now is the following:

INSERT INTO db1.x (a,b,c,d)
SELECT y.a, y.b, y.c, z.p 
FROM db2.y, db2.z 
WHERE 
    NOT y.a IN (SELECT x.a FROM db1.x) 
    AND y.a = z.a

This above code returns the error: 'duplicate key value violates unique constraint' even though such an error should be impossible due to checking if the primary key had already been inserted into the table.

Edit: The table has only 1 unique constraint which is the primary key, in the code above referred to as x.a

Answer 1

The error message is clear enough: you cannot insert because it violates a unique constraint that is defined in the table.

Since you are already checking for dups on the primary key, it is higly likely that your table has another unique constraint set up, which forbids the insert operation. That cannot be told for sure since your did not share the definition of your table.

You could change the query to check for that constraint as well before inserting.

Another option would be to use handy Postgres ON CONFLICT DO NOTHING clause to do the work for you. From the documentation:

The optional ON CONFLICT clause specifies an alternative action to raising a unique violation or exclusion constraint violation error. [...] ON CONFLICT DO NOTHING simply avoids inserting a row as its alternative action.

Consider this simpler query:

INSERT INTO db1.x (a,b,c,d)
    SELECT y.a, y.b, y.c, z.p FROM db2.y INNER JOIN db2.z ON y.a = z.a
ON CONFLICT DO NOTHING;

Note: implicit joins have fallen out of favor long ago (although perfectly functionals, they tend to make te query harder to understand); I modified that part of the query to use an explicit join.