Inkrementieren einer Gruppe von Werten in einem Feld mit UNIQUE-Beschränkung mit einer Abfrage, Postgres

Question

Ich habe eine Tabelle, in der ich ein numerisches Feld habe A die wie folgt festgelegt wird UNIQUE . Dieses Feld wird verwendet, um eine Reihenfolge anzugeben, in der eine bestimmte Aktion ausgeführt werden muss. Ich möchte eine UPDATE aller Werte, die z. B. größer sind als 3 . Zum Beispiel, Ich habe

A     
1
2
3
4
5

Jetzt möchte ich hinzufügen 1 auf alle Werte von A größer als 3 . Das Ergebnis wäre also

A     
1
2
3
5
6

Die Frage ist, ob es möglich ist, dies mit nur einer Abfrage zu tun? Denken Sie daran, dass ich eine UNIQUE Einschränkung der Spalte A .

Offensichtlich habe ich versucht

UPDATE my_table SET A = A + 1 WHERE A > 3;

aber es hat nicht funktioniert, da ich die Einschränkung auf dieses Feld habe.

Answer 1

PostgreSQL 9.0 und höher

PostgreSQL 9.0 hinzugefügt aufschiebbare eindeutige Beschränkungen was genau die Funktion ist, die Sie zu brauchen scheinen. Auf diese Weise wird die Einzigartigkeit zum Zeitpunkt der Übergabe und nicht zum Zeitpunkt der Aktualisierung geprüft.

Erstellen Sie die UNIQUE-Beschränkung mit dem Schlüsselwort DEFERRABLE:

ALTER TABLE foo ADD CONSTRAINT foo_uniq (foo_id) DEFERRABLE;

Später, bevor Sie die UPDATE-Anweisung ausführen, führen Sie dieselbe Transaktion durch:

SET CONSTRAINTS foo_uniq DEFERRED;

Alternativ können Sie die Einschränkung auch mit dem Befehl INITIALLY DEFERRED Schlüsselwort für die eindeutige Einschränkung selbst - Sie müssen also nicht SET CONSTRAINTS -- aber das könnte die Leistung Ihrer anderen Abfragen beeinträchtigen, die die Einschränkung nicht aufschieben müssen.

PostgreSQL 8.4 und älter

Wenn Sie die eindeutige Einschränkung nur zum Garantieren der Eindeutigkeit verwenden möchten - nicht als Ziel für einen Fremdschlüssel - dann könnte dieser Workaround helfen:

Fügen Sie zunächst eine boolesche Spalte wie is_temporary in der Tabelle, die vorübergehend zwischen aktualisierten und nicht aktualisierten Zeilen unterscheidet:

CREATE TABLE foo (value int not null, is_temporary bool not null default false);

Als nächstes erstellen Sie eine Teilweise eindeutiger Index die nur Zeilen betrifft, bei denen is_temporary=false ist:

CREATE UNIQUE INDEX ON foo (value) WHERE is_temporary=false;

Wenn Sie nun die von Ihnen beschriebenen Aktualisierungen vornehmen, führen Sie sie in zwei Schritten durch:

UPDATE foo SET is_temporary=true, value=value+1 WHERE value>3;
UPDATE foo SET is_temporary=false WHERE is_temporary=true;

Solange diese Anweisungen in einer einzigen Transaktion erfolgen, ist dies völlig sicher - andere Sitzungen werden die temporären Zeilen nie sehen. Der Nachteil ist, dass Sie die Zeilen zweimal schreiben müssen.

Beachten Sie, dass es sich hierbei lediglich um einen eindeutigen Index handelt, nicht um einen Einschränkung aber in der Praxis sollte das keine Rolle spielen.

Answer 2

Sie können dies mit einem einfachen Trick in 2 Abfragen tun:

Aktualisieren Sie zunächst Ihre Spalte mit +1, fügen Sie aber ein mit einem x(-1)-Faktor hinzu:

update my_table set A=(A+1)*-1  where A > 3.

Sie werden von 4,5,6 auf -5,-6,-7 umschalten

Zweitens: Konvertieren Sie die Operation zurück, um das positive Ergebnis wiederherzustellen:

update my_table set A=(A)*-1  where A < 0.

Sie werden haben: 5,6,7

Answer 3

Sie können dies mit einer Schleife tun. Ich mag diese Lösung nicht, aber sie funktioniert:

CREATE TABLE update_unique (id INT NOT NULL);
CREATE UNIQUE INDEX ux_id ON update_unique (id);
INSERT INTO update_unique(id) SELECT a FROM generate_series(1,100) AS foo(a);

DO $$
DECLARE v INT;
BEGIN
  FOR v IN SELECT id FROM update_unique WHERE id > 3 ORDER BY id DESC 
  LOOP
    UPDATE update_unique SET id = id + 1 WHERE id = v;
  END LOOP;
END;
$$ LANGUAGE 'plpgsql';

In PostgreSQL 8.4 müssen Sie möglicherweise eine Funktion erstellen, um dies zu tun, da Sie PL/PGSQL nicht willkürlich von einer Eingabeaufforderung mit DO ausführen können (zumindest nicht, soweit ich mich erinnere).