PostgreSQL multi-column unique constraint causes errors

Question

I am new to postgresql and have a question about multiple column unique constraint.

I got this error when tried to add rows to the table:

ERROR:  duplicate key value violates unique constraint "i_rb_on"
DETAIL:  Key (a_fk, b_fk)=(296, 16) already exists.

I used this code (short version):

INSERT INTO rb_on (a_fk, b_fk) SELECT a.pk, b.pk FROM A, B WHERE NOT EXISTS (SELECT * FROM rb_on WHERE a_fk=a.pk AND b_fk=b.pk);

i_rb_on is unique constraint / columns (a_fk, b_fk).

It seems that my WHERE NOT EXISTS doesn't provide a protection against the duplicate key error for this kind of unique key.

UPDATE:

INSERT INTO tabA (mark_done, log_time, tabB_fk, tabC_fk) 
SELECT FALSE, '2003-09-02 04:05:06', tabB.pk, tabC.pk FROM tabB, tabC, tabD, tabE, tabF 
WHERE (tabC.sf_id='SUMMER' AND tabC.sf_status IN(0,1) 
       AND tabE.inventory_status=0) 
 AND tabF.tabD_fk=tabD.pk 
 AND tabD.tabE_fk=tabE.pk 
 AND tabE.tabB_fk=tabB.pk 
 AND tabF.tabC_fk=tabC.pk 
 AND NOT EXISTS (SELECT * 
                 FROM tabA 
                 WHERE tabB_fk=tabB.pk AND tabC_fk=tabC.pk);

In tabA unique index:

CREATE UNIQUE INDEX i_tabA
  ON tabA
  USING btree
  (tabB_fk , tabC_fk );

Only one row (of many) must be inserted into the tabA.

score 2 · Answer 1 · answered Sep 03 '13 at 00:37

Your WHERE NOT EXISTS never provides proper protection against a unique violation. It only seems to most of the time. The WHERE NOT EXISTS can run concurrently with another insert, so the row is still inserted multiple times and all but one of the inserts causes a unique violation.

For that reason it's often better to just run the insert and let the violation happen if the row already exists.

I can't help you with the exact problem described unless you show the data (as SQL CREATE TABLE and INSERTs) and the real query.

BTW, please don't use old style A, B joins. Use A INNER JOIN B ON (...). It makes it easier to tell which join conditions are supposed to apply to which parts of the query, and harder to forget a join condition. You seem to have done that; you're attempting to insert a cartesian product. I suspect it's just an editing mistake in the query.

"just run the insert and let the violation happen": It's easier to ask forgiveness than it is to get permission. — mu is too short, Sep 03 '13 at 01:12

score 0 · Answer 2 · answered Sep 05 '13 at 00:11

I added LIMIT 1 to the end: ...WHERE tabB_fk=tabB.pk AND tabC_fk=tabC.pk) LIMIT1 ; and it did the trick.

I created a function with LIMIT 1 and ...EXCEPTION WHEN unique_violation THEN ... and it also worked.

But when LIMIT 1 and "NOT EXISTS" are used, I think, it is not necessary to use unique_violation error handling.

PostgreSQL multi-column unique constraint causes errors

2 Answers2