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

PostgreSQL depuis la version 9.5 a UPSERT syntaxe, avec SUR LE CONFLIT clause. avec la syntaxe suivante (similaire à celle de 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 de "upsert" dans les archives des groupes de messagerie de postgresql a permis de trouver ce qui suit un exemple de ce que vous voulez éventuellement faire, dans le manuel :

Exemple 38-2. Exceptions avec UPDATE/INSERT

Cet exemple utilise le traitement des exceptions pour effectuer un UPDATE ou un 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 existe peut-être un exemple de la manière de procéder en masse, en utilisant les CTE dans les versions 9.1 et supérieures, dans le fichier 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.

Answer 2

Attention : cette opération n'est pas sûre si elle est exécutée à partir de plusieurs sessions en même temps. (voir les mises en garde ci-dessous).

---

Une autre façon astucieuse de faire un "UPSERT" dans postgresql est de faire deux déclarations UPDATE/INSERT séquentielles qui sont chacune conçues pour réussir ou n'avoir 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);

L'UPDATE réussit si une ligne avec "id=3" existe déjà, sinon il n'a aucun effet.

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

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

Cette méthode fonctionne très bien lorsqu'elle est exécutée de manière isolée ou sur une table verrouillée, mais elle est sujette à des conditions de concurrence qui font qu'elle peut échouer avec une erreur de clé dupliquée si une ligne est insérée simultanément, ou qu'elle peut se terminer sans ligne insérée lorsqu'une ligne est supprimée simultanément. A SERIALIZABLE sur PostgreSQL 9.1 ou supérieur le traitera de manière fiable au prix d'un taux d'échec de sérialisation très élevé, ce qui signifie que vous devrez réessayer souvent. Voir pourquoi l'upsert est si compliqué qui traite de cette affaire de manière plus détaillée.

Cette approche est également sous réserve de mises à jour perdues dans read committed à moins que l'application ne vérifie le nombre de lignes concernées et ne s'assure que l'un ou l'autre des éléments suivants est bien présent insert ou le update affecté d'une rangée .

Answer 3

Avec PostgreSQL 9.1, cela peut être réalisé en utilisant un CTE inscriptible ( expression de 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 articles de blog :

• Upserting via Writeable CTE
• EN ATTENTE DE LA 9.1 - CTE INSCRIPTIBLE
• POURQUOI L'UPSERT EST SI COMPLIQUÉ ?

---

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

Answer 4

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

Voir la documentation .

A MySQL INSERT ... ON DUPLICATE KEY UPDATE peut être directement reformulé en un ON CONFLICT UPDATE . Ni l'un ni l'autre n'est une syntaxe standard SQL, ce sont tous deux des extensions spécifiques aux bases de données. Il y a de bonnes raisons MERGE n'a pas été utilisé pour cette une nouvelle syntaxe n'a pas été créée juste pour le plaisir. (La syntaxe de MySQL présente également des problèmes qui font qu'elle n'a pas été adoptée directement).

Par exemple, une 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 :

• Vous doit spécifier 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 s'il s'agissait d'un produit normal. UPDATE déclaration

Il a aussi quelques fonctionnalités intéressantes :

• Vous pouvez avoir un WHERE sur votre UPDATE (vous permettant de transformer efficacement ON CONFLICT UPDATE en 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 originales dans la table en utilisant le nom de la table. Ainsi, 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 le tableau. Vous pouvez utiliser l'un ou l'autre, ou les deux, dans le SET les expressions et WHERE clause.

Pour plus d'informations sur l'upsert, voir Comment faire un UPSERT (MERGE, INSERT ... ON DUPLICATE UPDATE) dans PostgreSQL ?

Answer 5

Je cherchais la même chose quand je suis venu ici, mais l'absence d'une fonction générique "upsert" m'a un peu ennuyé, alors j'ai pensé que vous pourriez simplement passer le sql de mise à jour et d'insertion comme arguments de cette fonction à partir du manuel.

qui 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 que pour faire ce que vous vouliez faire initialement, un "upsert" par lot, vous pourriez utiliser Tcl pour diviser le sql_update et boucler les mises à jour individuelles, la perte de performance 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 faible.