Comment faire un UPSERT (MERGE, INSERT ... ON DUPLICATE UPDATE) dans PostgreSQL ?

Question

Une question très fréquemment posée ici est de savoir comment faire un upsert, ce que MySQL appelle INSERT ... ON DUPLICATE UPDATE et les supports standards dans le cadre du MERGE fonctionnement.

Étant donné que PostgreSQL ne le supporte pas directement (avant la version 9.5), comment faire ? Considérez ce qui suit :

CREATE TABLE testtable (
    id integer PRIMARY KEY,
    somedata text NOT NULL
);

INSERT INTO testtable (id, somedata) VALUES
(1, 'fred'),
(2, 'bob');

Maintenant imaginez que vous voulez "upsert" les tuples (2, 'Joe') , (3, 'Alan') La nouvelle table des matières serait donc la suivante :

(1, 'fred'),
(2, 'Joe'),    -- Changed value of existing tuple
(3, 'Alan')    -- Added new tuple

C'est ce dont les gens parlent lorsqu'ils discutent d'un upsert . Il est essentiel que toute approche soit sûr en présence de transactions multiples travaillant sur la même table - soit en utilisant un verrouillage explicite, soit en se défendant contre les conditions de course qui en résultent.

Ce sujet est discuté en détail sur le site Insertion, sur mise à jour dupliquée dans PostgreSQL ? mais il s'agit d'alternatives à la syntaxe MySQL, et il s'est enrichi d'un certain nombre de détails non liés au fil du temps. Je travaille sur des réponses définitives.

Ces techniques sont également utiles pour "insérer s'il n'existe pas, sinon ne rien faire", c'est-à-dire "insérer ... en ignorant les clés dupliquées".

Answer 1

9.5 et plus récents :

Support de PostgreSQL 9.5 et plus récent INSERT ... ON CONFLICT (key) DO UPDATE (et ON CONFLICT (key) DO NOTHING ), c'est-à-dire l'upsert.

Comparaison avec ON DUPLICATE KEY UPDATE .

Explication rapide .

Pour l'utilisation, voir le manuel - notamment le conflit_action dans le diagramme syntaxique, et le texte explicatif .

Contrairement aux solutions pour les versions 9.4 et antérieures qui sont données ci-dessous, cette fonctionnalité fonctionne avec plusieurs lignes en conflit et ne nécessite pas de verrouillage exclusif ou de boucle de réessai.

Le commit ajoutant la fonctionnalité est ici y la discussion autour de son développement est ici .

---

Si vous êtes sous la version 9.5 et que vous n'avez pas besoin d'être rétrocompatible, vous pouvez arrêter de lire maintenant. .

---

9.4 et plus :

PostgreSQL ne dispose pas d'un système intégré de gestion de l'information. UPSERT (ou MERGE ), et il est très difficile de le faire de manière efficace face à une utilisation simultanée.

Cet article aborde le problème de manière très détaillée .

En général, vous devez choisir entre deux options :

• des opérations individuelles d'insertion/mise à jour dans une boucle de réessai ; ou
• Verrouiller la table et faire une fusion par lots

Boucle de réessai de ligne individuelle

L'utilisation d'insertions de rangées individuelles dans une boucle de réessai est l'option raisonnable si vous voulez que de nombreuses connexions essaient simultanément d'effectuer des insertions.

La documentation de PostgreSQL contient une procédure utile qui vous permet de faire cela dans une boucle à l'intérieur de la base de données. . Il protège contre les mises à jour perdues et les courses d'insertion, contrairement à la plupart des solutions naïves. Elle ne fonctionne que dans READ COMMITTED et n'est sûr que si c'est la seule chose que vous faites dans la transaction, cependant. La fonction ne fonctionnera pas correctement si des déclencheurs ou des clés uniques secondaires provoquent des violations uniques.

Cette stratégie est très inefficace. Chaque fois que cela est possible, vous devez mettre le travail en file d'attente et effectuer un upsert en masse comme décrit ci-dessous.

De nombreuses tentatives de solutions à ce problème ne prennent pas en compte les retours en arrière, ce qui entraîne des mises à jour incomplètes. Deux transactions s'affrontent, l'une d'entre elles réussit à INSERT l'autre obtient une erreur de clé dupliquée et fait un UPDATE à la place. Le site UPDATE blocs en attente de la INSERT pour faire un retour en arrière ou un commit. Lorsqu'il effectue un rollback, le UPDATE ne correspond à aucune rangée, donc même si la condition UPDATE commite qu'il n'a pas réellement fait l'upsert que vous attendiez. Vous devez vérifier le nombre de lignes du résultat et réessayer si nécessaire.

Certaines tentatives de solutions ne tiennent pas compte non plus des races SELECT. Si vous essayez la solution évidente et simple :

-- THIS IS WRONG. DO NOT COPY IT. It's an EXAMPLE.

BEGIN;

UPDATE testtable
SET somedata = 'blah'
WHERE id = 2;

-- Remember, this is WRONG. Do NOT COPY IT.

INSERT INTO testtable (id, somedata)
SELECT 2, 'blah'
WHERE NOT EXISTS (SELECT 1 FROM testtable WHERE testtable.id = 2);

COMMIT;

puis, lorsque deux fonctionnent en même temps, il y a plusieurs modes de défaillance. L'un d'entre eux est le problème déjà évoqué de la mise à jour et de la revérification. Un autre est celui où les deux UPDATE en même temps, en faisant correspondre les rangées zéro et en continuant. Ensuite, ils font tous les deux le EXISTS test, qui se produit avant le site INSERT . Les deux obtiennent des rangs zéro, donc les deux font le INSERT . L'un d'eux échoue avec une erreur de clé dupliquée.

C'est pourquoi vous avez besoin d'une boucle de réessai. Vous pourriez penser que vous pouvez éviter les erreurs de clés dupliquées ou les mises à jour perdues avec un SQL intelligent, mais ce n'est pas le cas. Vous devez vérifier le nombre de lignes ou gérer les erreurs de clés dupliquées (selon l'approche choisie) et réessayer.

Veuillez ne pas élaborer votre propre solution pour ce problème. Comme pour la mise en file d'attente des messages, c'est probablement une erreur.

Renvoi en vrac avec serrure

Il arrive parfois que l'on veuille effectuer un upsert en masse, c'est-à-dire que l'on souhaite fusionner un nouvel ensemble de données avec un ensemble de données existant plus ancien. Cette opération est largement est plus efficace que l'insertion de rangs individuels et doit être privilégiée chaque fois que cela est possible.

Dans ce cas, vous suivez généralement le processus suivant :

• CREATE a TEMPORARY tableau

• COPY ou insérer en vrac les nouvelles données dans la table temporaire

• LOCK la table cible IN EXCLUSIVE MODE . Cela permet à d'autres transactions de SELECT mais sans apporter de modifications au tableau.

• Faites un UPDATE ... FROM des enregistrements existants en utilisant les valeurs de la table temporaire ;

• Faites un INSERT de lignes qui n'existent pas déjà dans la table cible ;

• COMMIT en libérant la serrure.

Ainsi, pour l'exemple donné dans la question, l'utilisation d'une valeur multiple INSERT pour remplir la table temporaire :

BEGIN;

CREATE TEMPORARY TABLE newvals(id integer, somedata text);

INSERT INTO newvals(id, somedata) VALUES (2, 'Joe'), (3, 'Alan');

LOCK TABLE testtable IN EXCLUSIVE MODE;

UPDATE testtable
SET somedata = newvals.somedata
FROM newvals
WHERE newvals.id = testtable.id;

INSERT INTO testtable
SELECT newvals.id, newvals.somedata
FROM newvals
LEFT OUTER JOIN testtable ON (testtable.id = newvals.id)
WHERE testtable.id IS NULL;

COMMIT;

Lecture connexe

• Page wiki d'UPSERT
• UPSERTisms dans Postgres
• Insertion, sur mise à jour dupliquée dans PostgreSQL ?
• http://petereisentraut.blogspot.com/2010/05/merge-syntax.html
• Upsert avec une transaction
• Est-ce que SELECT ou INSERT dans une fonction est sujet à des conditions de course ?
• SQL MERGE sur le wiki PostgreSQL
• La façon la plus idiomatique d'implémenter UPSERT dans Postgresql aujourd'hui

Qu'en est-il MERGE ?

Norme SQL MERGE a en fait une sémantique de concurrence mal définie et ne convient pas pour l'upserting sans verrouiller une table au préalable.

Il s'agit d'une instruction OLAP très utile pour la fusion de données, mais ce n'est pas une solution utile pour l'insertion ascendante à sécurité concurrentielle. Il y a beaucoup de conseils aux personnes utilisant d'autres SGBD pour utiliser MERGE pour les upserts, mais c'est en fait faux.

Autres DBs :

• INSERT ... ON DUPLICATE KEY UPDATE dans MySQL
• MERGE à partir de MS SQL Server (mais voir ci-dessus à propos de MERGE problèmes)
• MERGE d'Oracle (mais voir ci-dessus à propos de MERGE problèmes)

Answer 2

Voici quelques exemples pour insert ... on conflict ... ( pg 9.5+ ) :

• Insérer, sur le conflit - ne rien faire .

  insert into dummy(id, name, size) values(1, 'new_name', 3)
  on conflict do nothing;`  

• Insérer, sur le conflit - faire une mise à jour spécifier la cible du conflit via colonne .

  insert into dummy(id, name, size) values(1, 'new_name', 3)
  on conflict(id)
  do update set name = 'new_name', size = 3;  

• Insérer, sur le conflit - faire une mise à jour spécifier la cible du conflit via nom de la contrainte .

  insert into dummy(id, name, size) values(1, 'new_name', 3)
  on conflict on constraint dummy_pkey
  do update set name = 'new_name', size = 4;

Answer 3

J'essaie de contribuer à une autre solution pour le problème d'insertion unique avec les versions pré-9.5 de PostgreSQL. L'idée est simplement d'essayer d'effectuer d'abord l'insertion, et dans le cas où l'enregistrement est déjà présent, de le mettre à jour :

do $$
begin 
  insert into testtable(id, somedata) values(2,'Joe');
exception when unique_violation then
  update testtable set somedata = 'Joe' where id = 2;
end $$;

Notez que cette solution peut être appliquée seulement s'il n'y a pas de suppression de lignes de la table .

Je ne connais pas l'efficacité de cette solution, mais elle me semble assez raisonnable.

Answer 4

SQLAlchemy upsert pour Postgres >=9.5

Étant donné que le grand article ci-dessus couvre de nombreuses approches SQL différentes pour les versions de Postgres (pas seulement les versions non 9.5 comme dans la question), je voudrais ajouter comment le faire dans SQLAlchemy si vous utilisez Postgres 9.5. Au lieu d'implémenter votre propre upsert, vous pouvez également utiliser les fonctions de SQLAlchemy (qui ont été ajoutées dans SQLAlchemy 1.1). Personnellement, je recommande d'utiliser ces fonctions, si possible. Non seulement pour des raisons de commodité, mais aussi parce que cela permet à PostgreSQL de gérer les conditions de course qui pourraient se produire.

Transfert d'une autre réponse que j'ai donnée hier ( https://stackoverflow.com/a/44395983/2156909 )

SQLAlchemy prend en charge ON CONFLICT maintenant avec deux méthodes on_conflict_do_update() y on_conflict_do_nothing() :

Copie de la documentation :

from sqlalchemy.dialects.postgresql import insert

stmt = insert(my_table).values(user_email='a@b.com', data='inserted data')
stmt = stmt.on_conflict_do_update(
    index_elements=[my_table.c.user_email],
    index_where=my_table.c.user_email.like('%@gmail.com'),
    set_=dict(data=stmt.excluded.data)
    )
conn.execute(stmt)

http://docs.sqlalchemy.org/en/latest/dialects/postgresql.html?highlight=conflict#insert-on-conflict-upsert

Answer 5

WITH UPD AS (UPDATE TEST_TABLE SET SOME_DATA = 'Joe' WHERE ID = 2 
RETURNING ID),
INS AS (SELECT '2', 'Joe' WHERE NOT EXISTS (SELECT * FROM UPD))
INSERT INTO TEST_TABLE(ID, SOME_DATA) SELECT * FROM INS

Testé sur Postgresql 9.3