问题描述
我有一个表项目",其中有一列位置".position 具有唯一且非空的约束.为了在位置 x 插入新行,我首先尝试增加后续项目的位置:
I have a table "items" with a column "position". position has a unique and not-null constraint. In order to insert a new row at position x I first try increment the positions of the subsequent items:
UPDATE items SET position = position + 1 WHERE position >= x;
这会导致违反唯一约束:
This results in a unique constraint violation:
ERROR: duplicate key value violates unique constraint
问题似乎是 PostgreSQL 执行更新的顺序.PostgreSQL 中的唯一约束 <9.0 不可延期,不幸的是,目前还不能选择使用 9.0.此外,UPDATE 语句不支持 ORDER BY 子句,以下也不起作用(仍然重复键违规):
The problem seems to be the order in which PostgreSQL performs the updates. Unique constraints in PostgreSQL < 9.0 aren't deferrable and unfortunately using 9.0 is currently not an option. Also, the UPDATE statement doesn't support an ORDER BY clause and the following doesn't work, too (still duplicate key violation):
UPDATE items SET position = position + 1 WHERE id IN (
SELECT id FROM items WHERE position >= x ORDER BY position DESC)
有人知道不涉及迭代代码中所有项目的解决方案吗?
Does somebody know a solution that doesn't involve iterating over all items in code?
推荐答案
另一个表,有多个唯一索引:
Another table, with multiple unique index:
create table utest(id integer, position integer not null, unique(id, position));
test=# d utest
Table "public.utest"
Column | Type | Modifiers
----------+---------+-----------
id | integer |
position | integer | not null
Indexes:
"utest_id_key" UNIQUE, btree (id, "position")
一些数据:
insert into utest(id, position) select generate_series(1,3), 1;
insert into utest(id, position) select generate_series(1,3), 2;
insert into utest(id, position) select generate_series(1,3), 3;
test=# select * from utest order by id, position;
id | position
----+----------
1 | 1
1 | 2
1 | 3
2 | 1
2 | 2
2 | 3
3 | 1
3 | 2
3 | 3
(9 rows)
我创建了一个以正确顺序更新位置值的过程:
I created a procedure that updates position values in the proper order:
create or replace function update_positions(i integer, p integer)
returns void as $$
declare
temprec record;
begin
for temprec in
select *
from utest u
where id = i and position >= p
order by position desc
loop
raise notice 'Id = [%], Moving % to %',
i,
temprec.position,
temprec.position+1;
update utest
set position = position+1
where position=temprec.position and id = i;
end loop;
end;
$$ language plpgsql;
一些测试:
test=# select * from update_positions(1, 2);
NOTICE: Id = [1], Moving 3 to 4
NOTICE: Id = [1], Moving 2 to 3
update_positions
------------------
(1 row)
test=# select * from utest order by id, position;
id | position
----+----------
1 | 1
1 | 3
1 | 4
2 | 1
2 | 2
2 | 3
3 | 1
3 | 2
3 | 3
(9 rows)
希望对你有帮助.
这篇关于PostgreSQL 8.3 中具有非空和唯一约束的增量字段的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!