PostgreSQL 9.0 及更高版本
添加了 PostgreSQL 9.0可延迟的唯一约束 http://www.depesz.com/index.php/2009/08/11/waiting-for-8-5-deferrable-uniqueness/,这正是您似乎需要的功能。这样,在提交时而不是更新时检查唯一性。
使用 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;
只要这些语句出现在单个事务中,这就是完全安全的——其他会话永远不会看到临时行。缺点是您需要将行写入两次。
请注意,这只是一个唯一索引,而不是一个约束,但实际上这并不重要。