Insertion, sur mise à jour dupliquée dans PostgreSQL ?

Question

Il y a plusieurs mois, j'ai appris d'une réponse sur Stack Overflow comment effectuer plusieurs mises à jour à la fois dans MySQL en utilisant la syntaxe suivante :

INSERT INTO table (id, field, field2) VALUES (1, A, X), (2, B, Y), (3, C, Z)
ON DUPLICATE KEY UPDATE field=VALUES(Col1), field2=VALUES(Col2);

Je suis maintenant passé à PostgreSQL et apparemment ce n'est pas correct. Il se réfère à toutes les tables correctes, je suppose donc qu'il s'agit de l'utilisation de mots-clés différents, mais je ne sais pas où dans la documentation de PostgreSQL cela est couvert.

Pour clarifier, je veux insérer plusieurs choses et, si elles existent déjà, les mettre à jour.

Answer 1

Selon le La documentation PostgreSQL de l'option INSERT déclaration en traitant le ON DUPLICATE KEY n'est pas pris en charge. Cette partie de la syntaxe est une extension propriétaire de MySQL.

Answer 2

J'ai le même problème pour la gestion des paramètres de compte sous forme de paires nom-valeur. Le critère de conception est que différents clients peuvent avoir différents ensembles de paramètres.

Ma solution, similaire à celle de JWP, consiste à effacer et remplacer en masse, en générant l'enregistrement de fusion dans votre application.

C'est une méthode à toute épreuve, indépendante de la plate-forme, et comme il n'y a jamais plus d'une vingtaine de paramètres par client, cela ne représente que trois appels à la base de données à faible charge - probablement la méthode la plus rapide.

L'alternative qui consiste à mettre à jour des lignes individuelles - vérifier les exceptions puis insérer - ou une combinaison des deux est un code hideux, lent et souvent cassé à cause (comme mentionné ci-dessus) du traitement non standard des exceptions SQL qui change d'une base de données à l'autre - ou même d'une version à l'autre.

 #This is pseudo-code - within the application:
 BEGIN TRANSACTION - get transaction lock
 SELECT all current name value pairs where id = $id into a hash record
 create a merge record from the current and update record
  (set intersection where shared keys in new win, and empty values in new are deleted).
 DELETE all name value pairs where id = $id
 COPY/INSERT merged records 
 END TRANSACTION

Answer 3

CREATE OR REPLACE FUNCTION save_user(_id integer, _name character varying)
  RETURNS boolean AS
$BODY$
BEGIN
    UPDATE users SET name = _name WHERE id = _id;
    IF FOUND THEN
        RETURN true;
    END IF;
    BEGIN
        INSERT INTO users (id, name) VALUES (_id, _name);
    EXCEPTION WHEN OTHERS THEN
            UPDATE users SET name = _name WHERE id = _id;
        END;
    RETURN TRUE;
END;

$BODY$
  LANGUAGE plpgsql VOLATILE STRICT

Answer 4

Pour la fusion de petits ensembles, l'utilisation de la fonction ci-dessus convient parfaitement. Cependant, si vous fusionnez de grandes quantités de données, je vous suggère d'examiner les options suivantes http://mbk.projects.postgresql.org

La meilleure pratique actuelle, à ma connaissance, est la suivante :

1. COPIER les données nouvelles/mises à jour dans la table temporaire (bien sûr, ou vous pouvez faire INSERT si le coût est correct).
2. Acquérir un verrou [facultatif] (le conseil est préférable aux verrous de table, IMO)
3. Fusionner. (la partie amusante)

Answer 5

UPDATE renvoie le nombre de lignes modifiées. Si vous utilisez JDBC (Java), vous pouvez alors comparer cette valeur à 0 et, si aucune ligne n'a été modifiée, lancer INSERT à la place. Si vous utilisez un autre langage de programmation, il est peut-être encore possible d'obtenir le nombre de lignes modifiées, consultez la documentation.

Ce n'est peut-être pas aussi élégant, mais vous avez un SQL beaucoup plus simple et plus facile à utiliser à partir du code d'appel. Par ailleurs, si vous écrivez la ligne script de dix lignes en PL/PSQL, vous devriez probablement avoir un test unitaire d'un type ou d'un autre rien que pour cela.