错误:重复的键值违反了 postgreSQL 中的唯一约束 [英] ERROR: duplicate key value violates unique constraint in postgreSQL

问题描述

我在更新表时在 postgresql 中遇到一个独特的约束问题.我有一个包含 3 列的表和一个列(internal_state)上的唯一约束.该表将只有两列，并且 internal_state 的值为 1,0.更新查询是

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 )

在 MSSqlserver 中运行此查询很好，但在 postgre 中它抛出唯一约束错误.我的理解是在更新所有行后在 SQLServer 中，然后只检查列上的约束，但在更新每一行后的 postgre 中，正在检查约束.所以在更新第一行(internal_state 值从 1 到 0)之后，postgre 会在更新第二行之前检查约束并抛出错误.

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?

推荐答案

http://www.postgresql.org/docs/9.0/static/sql-createtable.html 在非延迟唯一性约束"部分中 - 当 UNIQUE 或 PRIMARY KEY 约束不可延迟时，PostgreSQL 检查每当插入或修改一行时立即具有唯一性."

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."

将您的唯一约束更改为可延迟将推迟检查，直到更新结束.要么使用 SET CONSTRAINTS 在会话级别禁用(这是烦人的重复)，要么使用 deferrable 选项删除并重新创建唯一性约束(我不知道 ALTER 构造可以在不删除的情况下执行此操作).

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).

这篇关于错误:重复的键值违反了 postgreSQL 中的唯一约束的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！