我有一个表,其中我有一个数字字段A,设置为UNIQUE.该字段用于指示必须执行某些操作的顺序.我想对所有更大的值进行更新,例如,比3更高.例如,
我有
我有
A 1 2 3 4 5
现在,我想在A的所有值中加1,大于3.因此,结果将是
A 1 2 3 5 6
问题是,是否可以只使用一个查询来完成？请记住,我对列A有一个UNIQUE约束.
显然,我试过了
UPDATE my_table SET A = A + 1 WHERE A > 3;
但它不起作用,因为我对这个领域有约束.
解决方法
Postgresql 9.0及更高版本
Postgresql 9.0增加了deferrable unique constraints,这正是您似乎需要的功能.这样,在提交时而不是更新时检查唯一性.
使用DEFERRABLE关键字创建UNIQUE约束：
ALTER TABLE foo ADD CONSTRAINT foo_uniq (foo_id) DEFERRABLE;
稍后,在运行UPDATE语句之前,您将在同一事务中运行：
SET CONSTRAINTS foo_uniq DEFERRED;
或者,您可以在唯一约束本身上使用INITIALLY DEFERRED关键字创建约束 – 因此您不必运行SET CONSTRAINTS – 但这可能会影响其他查询的性能,而这些查询不需要推迟约束.
Postgresql 8.4及更早版本
如果您只想使用唯一约束来保证唯一性 – 而不是作为外键的目标 – 那么这种解决方法可能会有所帮助：
首先,将一个布尔列(如is_temporary)添加到临时区分更新和未更新行的表中：
CREATE TABLE foo (value int not null,is_temporary bool not null default false);
接下来创建一个仅影响is_temporary = false的行的部分唯一索引：
CREATE UNIQUE INDEX ON foo (value) WHERE is_temporary=false;
现在,每次都进行您描述的更新,您可以分两步运行它们：
UPDATE foo SET is_temporary=true,value=value+1 WHERE value>3; UPDATE foo SET is_temporary=false WHERE is_temporary=true;
只要这些语句出现在单个事务中,这将是完全安全的 – 其他会话永远不会看到临时行.缺点是你将两次写行.
请注意,这只是一个独特的索引,而不是约束,但在实践中它应该无关紧要.
