Question Insérer, sur une mise à jour en double dans PostgreSQL?


Il y a quelques 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, donc je suppose qu'il s'agit de différents mots-clés utilisés, mais je ne sais pas où cela est couvert dans la documentation de PostgreSQL.

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


519
2017-07-10 11:38


origine


Réponses:


PostgreSQL depuis la version 9.5 UPSERT syntaxe, avec SUR LE CONFLIT clause. avec la syntaxe suivante (similaire à MySQL)

INSERT INTO the_table (id, column_1, column_2) 
VALUES (1, 'A', 'X'), (2, 'B', 'Y'), (3, 'C', 'Z')
ON CONFLICT (id) DO UPDATE 
  SET column_1 = excluded.column_1, 
      column_2 = excluded.column_2;

La recherche dans les archives du groupe de courrier électronique de postgresql pour "upsert" conduit à trouver un exemple de faire ce que vous voulez faire, dans le manuel:

Exemple 38-2. Exceptions avec UPDATE / INSERT

Cet exemple utilise la gestion des exceptions pour exécuter UPDATE ou INSERT, selon le cas:

CREATE TABLE db (a INT PRIMARY KEY, b TEXT);

CREATE FUNCTION merge_db(key INT, data TEXT) RETURNS VOID AS
$$
BEGIN
    LOOP
        -- first try to update the key
        -- note that "a" must be unique
        UPDATE db SET b = data WHERE a = key;
        IF found THEN
            RETURN;
        END IF;
        -- not there, so try to insert the key
        -- if someone else inserts the same key concurrently,
        -- we could get a unique-key failure
        BEGIN
            INSERT INTO db(a,b) VALUES (key, data);
            RETURN;
        EXCEPTION WHEN unique_violation THEN
            -- do nothing, and loop to try the UPDATE again
        END;
    END LOOP;
END;
$$
LANGUAGE plpgsql;

SELECT merge_db(1, 'david');
SELECT merge_db(1, 'dennis');

Il y a peut-être un exemple de la façon de faire cela en gros, en utilisant des CTE en 9.1 et plus, dans le liste de diffusion des pirates:

WITH foos AS (SELECT (UNNEST(%foo[])).*)
updated as (UPDATE foo SET foo.a = foos.a ... RETURNING foo.id)
INSERT INTO foo SELECT foos.* FROM foos LEFT JOIN updated USING(id)
WHERE updated.id IS NULL;

Voir Réponse de a_horse_with_no_name pour un exemple plus clair.


355
2017-07-10 12:18



Attention: ceci n'est pas sûr s'il est exécuté à partir de plusieurs sessions en même temps (voir les mises en garde ci-dessous).


Une autre façon intelligente de faire un "UPSERT" dans postgresql est de faire deux instructions UPDATE / INSERT séquentielles qui sont chacune conçues pour réussir ou n'ont aucun effet.

UPDATE table SET field='C', field2='Z' WHERE id=3;
INSERT INTO table (id, field, field2)
       SELECT 3, 'C', 'Z'
       WHERE NOT EXISTS (SELECT 1 FROM table WHERE id=3);

Le UPDATE réussira si une ligne avec "id = 3" existe déjà, sinon elle n'a aucun effet.

L'INSERT réussira seulement si la ligne avec "id = 3" n'existe pas déjà.

Vous pouvez combiner ces deux en une seule chaîne et les exécuter avec une seule instruction SQL exécutée à partir de votre application. Les exécuter ensemble dans une seule transaction est fortement recommandé.

Cela fonctionne très bien lorsqu'il est exécuté de manière isolée ou sur une table verrouillée, mais est soumis à des conditions de concurrence qui peuvent entraîner l'échec d'une erreur de clé dupliquée si une ligne est insérée simultanément ou peut se terminer sans qu'aucune ligne ne soit insérée simultanément. . UNE SERIALIZABLE transaction sur PostgreSQL 9.1 ou supérieur le traitera de manière fiable au prix d'un taux de défaillance de sérialisation très élevé, ce qui signifie que vous devrez réessayer beaucoup. Voir pourquoi est si compliqué, qui traite de ce cas plus en détail.

Cette approche est également sous réserve de mises à jour perdues dans read committed isolation à moins que l'application vérifie le nombre de lignes affectées et vérifie que insert ou la update affecté une rangée.


404
2018-06-29 22:06



Avec PostgreSQL 9.1, ceci peut être réalisé en utilisant un CTE inscriptible (expression de la table commune):

WITH new_values (id, field1, field2) as (
  values 
     (1, 'A', 'X'),
     (2, 'B', 'Y'),
     (3, 'C', 'Z')

),
upsert as
( 
    update mytable m 
        set field1 = nv.field1,
            field2 = nv.field2
    FROM new_values nv
    WHERE m.id = nv.id
    RETURNING m.*
)
INSERT INTO mytable (id, field1, field2)
SELECT id, field1, field2
FROM new_values
WHERE NOT EXISTS (SELECT 1 
                  FROM upsert up 
                  WHERE up.id = new_values.id)

Voir ces entrées de blog:


Notez que cette solution ne ne pas empêcher une violation de clé unique, mais il n'est pas vulnérable aux mises à jour perdues.
Voir le suivi par Craig Ringer sur dba.stackexchange.com


215
2018-01-02 15:00



Dans PostgreSQL 9.5 et plus récent, vous pouvez utiliser INSERT ... ON CONFLICT UPDATE.

Voir La documentation.

Un MySQL INSERT ... ON DUPLICATE KEY UPDATE peut être directement reformulé à un ON CONFLICT UPDATE. La syntaxe standard SQL n'est pas non plus, ce sont les deux extensions spécifiques à la base de données. Il y a de bonnes raisons MERGE n'a pas été utilisé pour cela, une nouvelle syntaxe n'a pas été créée juste pour s'amuser. (La syntaxe de MySQL a également des problèmes qui signifient qu'il n'a pas été adopté directement).

par exemple. configuration donnée:

CREATE TABLE tablename (a integer primary key, b integer, c integer);
INSERT INTO tablename (a, b, c) values (1, 2, 3);

la requête MySQL:

INSERT INTO tablename (a,b,c) VALUES (1,2,3)
  ON DUPLICATE KEY UPDATE c=c+1;

devient:

INSERT INTO tablename (a, b, c) values (1, 2, 10)
ON CONFLICT (a) DO UPDATE SET c = tablename.c + 1;

Différences:

  • Toi doit spécifiez le nom de la colonne (ou le nom de la contrainte unique) à utiliser pour la vérification de l'unicité. C'est le ON CONFLICT (columnname) DO

  • Le mot-clé SET doit être utilisé, comme si c'était normal UPDATE déclaration

Il a aussi de belles fonctionnalités:

  • Vous pouvez avoir un WHERE clause sur votre UPDATE (vous permettant de tourner efficacement ON CONFLICT UPDATE dans ON CONFLICT IGNORE pour certaines valeurs)

  • Les valeurs proposées pour l'insertion sont disponibles en tant que variable de ligne EXCLUDED, qui a la même structure que la table cible. Vous pouvez obtenir les valeurs d'origine dans la table en utilisant le nom de la table. Donc dans ce cas EXCLUDED.c sera 10 (parce que c'est ce que nous avons essayé d'insérer) et "table".c sera 3 parce que c'est la valeur actuelle dans la table. Vous pouvez utiliser l'un ou l'autre SET expressions et WHERE clause.

Pour le fond sur upsert voir Comment UPSERT (MERGE, INSERT ... ON DUPLICATE UPDATE) dans PostgreSQL?


97
2018-05-08 07:53



Je cherchais la même chose quand je suis venu ici, mais le manque d'une fonction générique "upsert" me dérange un peu alors je pensais que vous pouviez passer la mise à jour et insérer sql comme arguments sur cette fonction du manuel

cela ressemblerait à ceci:

CREATE FUNCTION upsert (sql_update TEXT, sql_insert TEXT)
    RETURNS VOID
    LANGUAGE plpgsql
AS $$
BEGIN
    LOOP
        -- first try to update
        EXECUTE sql_update;
        -- check if the row is found
        IF FOUND THEN
            RETURN;
        END IF;
        -- not found so insert the row
        BEGIN
            EXECUTE sql_insert;
            RETURN;
            EXCEPTION WHEN unique_violation THEN
                -- do nothing and loop
        END;
    END LOOP;
END;
$$;

et peut-être pour faire ce que vous vouliez initialement faire, lot "upsert", vous pouvez utiliser Tcl pour diviser le sql_update et boucler les mises à jour individuelles, le hit de préformance sera très faible voir http://archives.postgresql.org/pgsql-performance/2006-04/msg00557.php

le coût le plus élevé est l'exécution de la requête à partir de votre code, du côté de la base de données, le coût d'exécution est beaucoup plus petit


16
2017-09-16 16:13



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

L'approche la plus correcte consiste à utiliser la fonction, comme celle de docs.

Une autre solution (bien que pas sûre) est de faire la mise à jour avec le retour, vérifier quelles lignes étaient mises à jour, et insérer le reste d'entre eux

Quelque chose dans le genre de:

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 id: 2 a été retourné:

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

Bien sûr, il va renflouer tôt ou tard (dans un environnement concurrent), car il y a une condition de concurrence claire ici, mais généralement cela fonctionnera.

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


12
2017-07-10 12:04



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

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

Vous vouliez pouvoir réinsérer des lignes avec des valeurs mises à jour, ou les créer si elles n'existaient pas déjà. Saisi sur le customer_id 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: Cela risque d'échouer si des insertions simultanées sont en cours, car cela générera des exceptions de type unique_violation. Toutefois, la transaction non terminée se poursuivra et réussira, et vous devrez simplement répéter la transaction terminée.

Cependant, s'il y a des tonnes d'inserts qui se passent tout le temps, vous devrez mettre un verrou de table autour des instructions insert: Le verrouillage SHARE ROW EXCLUSIVE empêchera toute opération qui pourrait insérer, supprimer ou 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 faites aucune opération, utilisez les verrous de conseil à la place.

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


8
2018-05-10 23:18



Personnalisé "upsert" fonction 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 l'exécution, faites quelque chose comme ceci:

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

Est important de mettre double dollar-virgule pour éviter les erreurs de compilation

  • vérifiez la vitesse ...

7
2017-09-21 20:55



J'ai le même problème pour gérer les paramètres de compte que les paires de valeurs de nom. Le critère de conception est que différents clients peuvent avoir des ensembles de paramètres différents.

Ma solution, similaire à JWP est d'effacer et de remplacer en bloc, générant l'enregistrement de fusion au sein de votre application.

Ceci est assez à l'épreuve des balles, indépendant de la plate-forme et puisqu'il n'y a jamais plus de 20 paramètres par client, ceci est seulement 3 appels de charge db assez bas - probablement la méthode la plus rapide.

L'alternative de mettre à jour des lignes individuelles - vérifier les exceptions puis insérer - ou une combinaison de code hideux, lent et souvent casse car (comme mentionné ci-dessus) la gestion des exceptions SQL non standard passant de db à db - ou même libérer à libérer.

 #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

5
2017-10-21 06:11



Similaire à la réponse la plus aimée, mais fonctionne un peu plus vite:

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)

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


5
2017-07-29 14:48