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

Il n'y a pas de commande simple pour le faire.

L'approche la plus correcte est d'utiliser la fonction, comme celle de docs .

Une autre solution (bien que moins sûre) est de faire une mise à jour avec retour, de vérifier quelles lignes ont été mises à jour, et d'insérer le reste des lignes.

Quelque chose du genre :

update table
set column = x.column
from (values (1,'aa'),(2,'bb'),(3,'cc')) as x (id, column)
where table.id = x.id
returning id;

en supposant que l'id:2 a été retourné :

insert into table (id, column) values (1, 'aa'), (3, 'cc');

Bien sûr, il se plantera tôt ou tard (dans un environnement concurrent), car il y a clairement une condition de course, mais en général, il fonctionnera.

Voici un un article plus long et plus complet sur le sujet .

Answer 2

J'utilise cette fonction pour fusionner

CREATE OR REPLACE FUNCTION merge_tabla(key INT, data TEXT)
  RETURNS void AS
$BODY$
BEGIN
    IF EXISTS(SELECT a FROM tabla WHERE a = key)
        THEN
            UPDATE tabla SET b = data WHERE a = key;
        RETURN;
    ELSE
        INSERT INTO tabla(a,b) VALUES (key, data);
        RETURN;
    END IF;
END;
$BODY$
LANGUAGE plpgsql

Answer 3

Personnellement, j'ai mis en place une "règle" attachée à l'instruction d'insertion. Supposons que vous ayez une table "dns" qui enregistre les hits dns par client sur une base temporelle :

CREATE TABLE dns (
    "time" timestamp without time zone NOT NULL,
    customer_id integer NOT NULL,
    hits integer
);

Vous vouliez être en mesure de réinsérer des lignes avec des valeurs mises à jour, ou de les créer si elles n'existaient pas déjà. Avec comme clé l'identifiant du client et l'heure. Quelque chose comme ça :

CREATE RULE replace_dns AS 
    ON INSERT TO dns 
    WHERE (EXISTS (SELECT 1 FROM dns WHERE ((dns."time" = new."time") 
            AND (dns.customer_id = new.customer_id)))) 
    DO INSTEAD UPDATE dns 
        SET hits = new.hits 
        WHERE ((dns."time" = new."time") AND (dns.customer_id = new.customer_id));

Mise à jour : cette opération risque d'échouer si des insertions simultanées ont lieu, car elle générera des exceptions de type unique_violation. Toutefois, la transaction non terminée se poursuivra et réussira, et il vous suffira de répéter la transaction terminée.

Cependant, si des tonnes d'insertions se produisent en permanence, vous voudrez mettre un verrou de table autour des instructions d'insertion : Le verrouillage SHARE ROW EXCLUSIVE empêchera toute opération susceptible d'insérer, de supprimer ou de mettre à jour des lignes dans votre table cible. Cependant, les mises à jour qui ne mettent pas à jour la clé unique sont sûres, donc si vous ne prévoyez aucune opération de ce type, utilisez plutôt des verrous consultatifs.

De plus, la commande COPY n'utilise pas de RULES, donc si vous insérez avec COPY, vous devrez utiliser des triggers à la place.

Answer 4

Similaire à la réponse la plus appréciée, mais fonctionne légèrement plus rapidement :

WITH upsert AS (UPDATE spider_count SET tally=1 WHERE date='today' RETURNING *)
INSERT INTO spider_count (spider, tally) SELECT 'Googlebot', 1 WHERE NOT EXISTS (SELECT * FROM upsert)

(source : http://www.the-art-of-web.com/sql/upsert/ )

Answer 5

J'ai personnalisé la fonction "upsert" ci-dessus, si vous voulez INSÉRER ET REMPLACER :

`

 CREATE OR REPLACE FUNCTION upsert(sql_insert text, sql_update text)

 RETURNS void AS
 $BODY$
 BEGIN
    -- first try to insert and after to update. Note : insert has pk and update not...

    EXECUTE sql_insert;
    RETURN;
    EXCEPTION WHEN unique_violation THEN
    EXECUTE sql_update; 
    IF FOUND THEN 
        RETURN; 
    END IF;
 END;
 $BODY$
 LANGUAGE plpgsql VOLATILE
 COST 100;
 ALTER FUNCTION upsert(text, text)
 OWNER TO postgres;`

Et après avoir exécuté, faites quelque chose comme ceci :

SELECT upsert($$INSERT INTO ...$$,$$UPDATE... $$)

Il est important de mettre le double dollar-virgule pour éviter les erreurs de compilation.

• vérifier la vitesse...