我有一个列sort_order,它有一个唯一的约束.
Postgres 9.5上的以下sql失败：
Postgres 9.5上的以下sql失败：
UPDATE test SET sort_order = sort_order + 1; -- [23505] ERROR: duplicate key value violates unique constraint "test_sort_order_key" -- Detail: Key (sort_order)=(2) already exists.
显然,如果sort_order值在更新之前是唯一的,那么在更新之后它们仍然是唯一的.为什么是这样？
同样的声明在Oracle和MS sql上运行良好,但在MysqL和sqlite上也失败了.
DROP TABLE IF EXISTS test;
CREATE TABLE test (
val TEXT,sort_order INTEGER NOT NULL UNIQUE
);
INSERT INTO test
VALUES ('A',1),('B',2);
解决方法
Postgres决定在与sql标准中提出的时间不同的时间检查IMMEDIATELY类型的约束.
具体来说,SET CONSTRAINTS州的文件(强调我的)：
NOT NULL and CHECK constraints are always checked immediately when a row is inserted or modified (not at the end of the statement). Uniqueness and exclusion constraints that have not been declared DEFERRABLE are also checked immediately.
Postgres选择使用导致sort_order临时冲突的计划执行此查询,并且IMMEDIATELY失败.请注意,这意味着对于相同的模式和相同的数据,相同的查询可能会起作用或失败,具体取决于执行计划.
您必须使约束DEFERRABLE或DEFERRABLE INITIALLY DEFERRED,这会延迟约束的验证,直到事务结束或直到执行语句SET CONSTRAINTS … IMMEDIATE.
