ERROR: duplicate key value violates unique constraint in postgreSQL

Question

i am getting a unique constraint issue in postgresql while updating a table. I have a table with 3 columns and an unique constraint on one of the column(internal_state). This table will have only two columns and values for internal_state are 1,0. The update query is

UPDATE backfeed_state SET internal_state = internal_state - 1 WHERE EXISTS (SELECT 1 FROM backfeed_state d2 WHERE d2.internal_state = 1 )

Running this query is fine in MSSqlserver but in postgre it is throwing unique constraint error. What i understand is in SQLServer after updating all the rows then only constraint on the columns are checking but in postgre after updating each row, constraints are checking. So after updating the first row(internal_state value from 1 to 0) postgre is checking the constraint and throwing error even before updating the second row.

Is there a way to avoid this situation?

yieldsfalsehood · Accepted Answer · 2011-07-11 14:32:21Z

5

http://www.postgresql.org/docs/9.0/static/sql-createtable.html in section "Non-deferred Uniqueness Constraints" - "When a UNIQUE or PRIMARY KEY constraint is not deferrable, PostgreSQL checks for uniqueness immediately whenever a row is inserted or modified."

Changing your unique constraint to deferrable will hold off checking until the end of the update. Either use SET CONSTRAINTS to disable at the session level (which is annoyingly repetitive) or drop and re-create the uniqueness constraint with the deferrable option (I'm not aware of an ALTER construct to do that without dropping).

edited Jul 11, 2011 at 14:32

answered Jul 11, 2011 at 14:19

yieldsfalsehood

3,1051 gold badge21 silver badges15 bronze badges

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

4 Comments

subash Over a year ago

hi we are using postgres8.4 where this DEFERRABLE concept is not implemented for unique constraint. Is there any other workaround or alternative for this

yieldsfalsehood Over a year ago

I mis-read your code; I would start with a simpler re-write like this: UPDATE backfeed_state SET internal_state = internal_state - 1 WHERE internal_state = 1. If your intention is to "flip" the 1's and 0's you could try removing the uniqueness, doing the update with SET internal_state = 1 - internal_state, then add back the uniqueness (assuming you really want to retain that constraint).

subash Over a year ago

i am updateing the records only if i have at least one row with internal_state value '1'. thats why i have the condition WHERE EXISTS (SELECT 1 FROM backfeed_state d2 WHERE d2.internal_state = 1 )

Tometzky Over a year ago

@subash - it does not make sense - you said you have always two rows of which always one has internal_state=0 and the other internal_state=1. So your where exists will always be true.

Collectives™ on Stack Overflow

ERROR: duplicate key value violates unique constraint in postgreSQL

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related