Inkrementieren Sie das Feld mit nicht null und eindeutiger Einschränkung in PostgreSQL 8.3

Ich habe eine Tabelle "Artikel" mit einer Spalte "Position". Position hat eine eindeutige und Nicht-Null-Einschränkung. Um eine neue Zeile an der Position x einzufügen, versuche ich zuerst die Positionen der folgenden Elemente zu erhöhen:

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

Dies führt zu einer eindeutigen Integritätsverletzung:

 ERROR: duplicate key value violates unique constraint 

Das Problem scheint die Reihenfolge zu sein, in der PostgreSQL die Aktualisierungen durchführt. Eindeutige Einschränkungen in PostgreSQL <9.0 sind nicht aufschiebbar und leider ist die Verwendung von 9.0 derzeit keine Option. Außerdem unterstützt die UPDATE-statement keine ORDER BY-Klausel, und Folgendes funktioniert ebenfalls nicht (weiterhin doppelte Schlüsselverletzung):

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

Kennt jemand eine Lösung, bei der nicht alle Elemente im Code durchlaufen werden?

Solutions Collecting From Web of "Inkrementieren Sie das Feld mit nicht null und eindeutiger Einschränkung in PostgreSQL 8.3"

Eine weitere Tabelle mit mehreren eindeutigen Indizes:

 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") 

data:

 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) 

Ich habe eine Prozedur erstellt, die die Positionswerte in der richtigen Reihenfolge aktualisiert:

 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; 

Einige Tests:

 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) 

Ich hoffe es hilft.

Da PostgreSQL den vollständigen Satz von Transaktions-DDLs unterstützt, können Sie leicht Folgendes tun:

 create table utest(id integer unique not null); insert into utest(id) select generate_series(1,4); 

Die Tabelle sieht jetzt so aus:

 test=# \d utest Table "public.utest" Column | Type | Modifiers --------+---------+----------- id | integer | not null Indexes: "utest_id_key" UNIQUE, btree (id) test=# select * from utest; id ---- 1 2 3 4 (4 rows) 

Und jetzt die ganze Magie:

 begin; alter table utest drop constraint utest_id_key; update utest set id = id + 1; alter table utest add constraint utest_id_key unique(id); commit; 

Danach haben wir:

 test=# \d utest Table "public.utest" Column | Type | Modifiers --------+---------+----------- id | integer | not null Indexes: "utest_id_key" UNIQUE, btree (id) test=# select * from utest; id ---- 2 3 4 5 (4 rows) 

Diese Lösung hat einen Nachteil: Sie muss die gesamte Tabelle sperren, aber vielleicht ist das hier kein Problem.

Die "Korrektur" -Lösung könnte darin bestehen, die Einschränkung DEFERRABLE zu machen

 ALTER TABLE channels ADD CONSTRAINT channels_position_unique unique("position") DEFERRABLE INITIALLY IMMEDIATE 

Setzen Sie diese Einschränkung auf DEFERRED, wenn Sie sie inkrementieren und auf IMMEDIATE zurücksetzen, sobald Sie fertig sind.

 SET CONSTRAINTS channels_position_unique DEFERRED; UPDATE channels SET position = position+1 WHERE position BETWEEN 1 AND 10; SET CONSTRAINTS channels_position_unique IMMEDIATE;