Swap unique colonne indexée valeurs dans la base de données

Question

J'ai une table de base de données et l'un des champs (pas la clé primaire) est d'avoir un index unique sur elle. Maintenant, je veux permuter les valeurs dans cette colonne pour les deux lignes. Comment cela pourrait-il être fait? Deux hacks que je connais sont:

1. Supprimer les deux lignes et les insérer
2. Mise à jour des lignes avec une autre valeur et le swap et ensuite mettre à jour à la valeur réelle.

Mais je ne veux pas y aller car ils ne semblent pas être la solution appropriée au problème. Quelqu'un pourrait-il m'aider?

Answer 1

Le mot magique est REPORTABLE ici:

DROP TABLE ztable CASCADE;
CREATE TABLE ztable
    ( id integer NOT NULL PRIMARY KEY
    , payload varchar
    );
INSERT INTO ztable(id,payload) VALUES (1,'one' ), (2,'two' ), (3,'three' );
SELECT * FROM ztable;


    -- This works, because there is no constraint
UPDATE ztable t1
SET payload=t2.payload
FROM ztable t2
WHERE t1.id IN (2,3)
AND t2.id IN (2,3)
AND t1.id <> t2.id
    ;
SELECT * FROM ztable;

ALTER TABLE ztable ADD CONSTRAINT OMG_WTF UNIQUE (payload)
    DEFERRABLE INITIALLY DEFERRED
    ;

    -- This should also work, because the constraint 
    -- is deferred until "commit time"
UPDATE ztable t1
SET payload=t2.payload
FROM ztable t2
WHERE t1.id IN (2,3)
AND t2.id IN (2,3)
AND t1.id <> t2.id
    ;
SELECT * FROM ztable;

RÉSULTAT:

DROP TABLE
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "ztable_pkey" for table "ztable"
CREATE TABLE
INSERT 0 3
 id | payload
----+---------
  1 | one
  2 | two
  3 | three
(3 rows)

UPDATE 2
 id | payload
----+---------
  1 | one
  2 | three
  3 | two
(3 rows)

NOTICE:  ALTER TABLE / ADD UNIQUE will create implicit index "omg_wtf" for table "ztable"
ALTER TABLE
UPDATE 2
 id | payload
----+---------
  1 | one
  2 | two
  3 | three
(3 rows)

Answer 2

Je pense que vous devriez aller pour la solution 2. Il n'y a pas de "swap" de la fonction dans n'importe quel SQL variante que je connais.

Si vous avez besoin de le faire régulièrement, je suggère la solution 1, en fonction de la façon dont les autres parties du logiciel sont à l'aide de ces données. Vous pouvez avoir des problèmes de verrouillage si vous ne faites pas attention.

Mais en bref: il n'y a pas d'autre solution que celles que vous avez fournis.

Answer 3

Je pense aussi que le #2 est le meilleur pari, mais je voudrais être sûr d'envelopper dans une transaction dans le cas où quelque chose se passe mal à la mi-mise à jour.

Une alternative (puisque vous l'avez demandé) à la mise à jour de l'Index Unique de valeurs avec différentes valeurs de mettre à jour toutes les autres valeurs dans les lignes à celle de l'autre ligne. Faire cela signifie que vous pouvez laisser l'Unique Indice des valeurs, et à la fin, vous vous retrouvez avec les données que vous souhaitez. Attention tout de même, dans le cas de certains autres références de table cette table dans une relation de Clé Étrangère, que toutes les relations dans la base de données restent intactes.

Answer 4

J'ai le même problème. Voici mon approche proposée dans PostgreSQL. Dans mon cas, mon index unique est une valeur de la séquence, la définition explicite de l'utilisateur-commande sur mes lignes. L'utilisateur shuffle lignes autour de dans une web-app, puis validez les modifications.

J'ai l'intention d'ajouter un "avant" déclencheur. Dans ce déclencheur, chaque fois que mon unique valeur de l'index est mis à jour, je vais regarder pour voir si une autre ligne détient déjà ma nouvelle valeur. Si oui, je vais leur donner mon ancienne valeur, et effectivement voler la valeur hors d'eux.

J'espère que PostgreSQL me permettra de faire ce shuffle en avant de déclencher.

Je vais poster en arrière et laissez-vous savez mon kilométrage.