在PostgreSQL 8.3中,增加具有非空和唯一约束的字段[英] Increment field with not null and unique constraint in PostgreSQL 8.3

本文是小编为大家收集整理的关于在PostgreSQL 8.3中,增加具有非空和唯一约束的字段的处理方法,想解了在PostgreSQL 8.3中,增加具有非空和唯一约束的字段的问题怎么解决?在PostgreSQL 8.3中,增加具有非空和唯一约束的字段问题的解决办法?那么可以参考本文帮助大家快速定位并解决问题。

问题描述

我有一个表"项目",其中有一列"位置".position 具有唯一且非空的约束.为了在位置 x 插入新行,我首先尝试增加后续项目的位置:

UPDATE items SET position = position + 1 WHERE position >= x;

这会导致违反唯一约束:

ERROR:  duplicate key value violates unique constraint

问题似乎是 PostgreSQL 执行更新的顺序.PostgreSQL 中的唯一约束 <9.0 不可延期,不幸的是,目前还不能选择使用 9.0.此外,UPDATE 语句不支持 ORDER BY 子句,以下也不起作用(仍然重复键违规):

UPDATE items SET position = position + 1 WHERE id IN (
  SELECT id FROM items WHERE position >= x ORDER BY position DESC)

有人知道不涉及迭代代码中所有项目的解决方案吗?

推荐答案

另一个表,有多个唯一索引:

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)

我创建了一个以正确顺序更新位置值的过程:

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)

希望对你有帮助.

本文地址:https://www.itbaoku.cn/post/1763909.html