Comment faire pour UPSERT (fusionner, insérer ... sur la mise à jour Dupliquer) dans PostgreSQL?

une question très fréquemment posée ici est comment faire un upsert, qui est ce que MySQL appelle INSERT ... ON DUPLICATE UPDATE et les supports standard dans le cadre de l'opération MERGE .

étant donné que PostgreSQL ne le supporte pas directement (avant la page 9.5), Comment faites-vous cela? Considérons 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') , de sorte que le nouveau contenu de la table serait:

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

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

ce sujet est longuement discuté à Insert, on duplicate update in PostgreSQL? , mais il s'agit d'alternatives à la syntaxe MySQL, et elle a poussé un peu de détails sans rapport 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.-à-d. "insérer ... sur la clé dupliquer ignorer".

208
demandé sur Community 2013-06-24 06:56:05

6 réponses

9.5 et plus récents:

PostgreSQL 9.5 et support Plus Récent INSERT ... ON CONFLICT UPDATE (et ON CONFLICT DO NOTHING ), i.e. upsert.

comparaison avec ON DUPLICATE KEY UPDATE .

explication Rapide .

pour l'usage voir le manuel - spécifiquement la clause conflict_action dans le diagramme de syntaxe, et le texte explicatif .

Contrairement aux solutions pour 9.4 et plus qui sont données ci-dessous, cette fonctionnalité fonctionne avec plusieurs lignes de conflit et il ne nécessite pas de verrouillage exclusif ou une boucle de Ré-essai.

La validation de l'ajout de la fonctionnalité est ici et "1519620920," le débat autour de sa mise en place est ici .


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


9.4 et plus vieux:

PostgreSQL n'a pas d'installation intégrée UPSERT (ou MERGE ), et il est très difficile de le faire efficacement en cas d'utilisation simultanée.

cet article traite du problème en détail .

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

  • opérations D'insertion/de mise à jour individuelles dans une boucle de reprise; ou
  • verrouillage de la table et fusion par lots

Single row retry loop

en utilisant des upserts de ligne individuels dans une boucle de Ré-essai est l'option raisonnable si vous voulez beaucoup de connexions simultanément en essayant d'effectuer des inserts.

The PostgreSQL la documentation contient une procédure utile qui vous permettra de le faire dans une boucle à l'intérieur de la base de données . Il protège contre les mises à jour perdues et insère des races, contrairement à la plupart des solutions naïves. Il ne fonctionne qu'en READ COMMITTED mode et n'est sûr que si c'est la seule chose que vous faites dans la transaction. La fonction ne fonctionnera pas correctement si des déclencheurs ou des Clés uniques secondaires causent des violations uniques.

Cette stratégie est très inefficace. Dans la mesure du possible, vous devriez faites la queue pour le travail et faites un upsert en vrac comme décrit ci-dessous à la place.

beaucoup de tentatives de solutions à ce problème ne tiennent pas compte des retraits, de sorte qu'ils entraînent des mises à jour incomplètes. Deux transactions vont de pair; l'une avec succès INSERT s, l'autre avec une erreur clé dupliquée et fait UPDATE à la place. Le bloc UPDATE attend le bloc INSERT pour se rétracter ou s'engager. Quand il recule, la condition re-check UPDATE correspond à zéro. lignes, donc même si le UPDATE engage il n'a pas réellement fait l'upsert que vous attendiez. Vous devez vérifier la ligne de résultat compte et ré-essayer si nécessaire.

certaines des solutions essayées ne tiennent pas compte des races choisies. Si vous essayez l'évident 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;

ensuite, lorsque deux essais sont effectués à la fois, il y a plusieurs modes de défaillance. L'une est la question déjà discutée avec une mise à jour revérification. Une autre est où les deux UPDATE à la en même temps, correspondant à zéro ligne et continuant. Puis ils font tous les deux le test EXISTS , qui se produit avant le INSERT . Les deux n'ont pas de rang, donc les deux font le INSERT . Un échec 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 prévenir les erreurs clés dupliquées ou les mises à jour perdues avec clever SQL, mais vous ne pouvez pas. Vous devez vérifier le nombre de lignes ou gérer les erreurs clés dupliquées (selon le approche choisie) et réessayez.

s'il vous Plaît ne pas rouler votre propre solution pour cela. Comme avec la file d'attente de message, c'est probablement mal.

vrac upsert avec verrouillage

Parfois, vous souhaitez faire un bloc upsert, où vous avez un nouveau jeu de données que vous souhaitez fusionner dans un ancien jeu de données. Il s'agit de très plus efficace que les rames individuelles upserts et devrait être préféré chaque fois que possible.

dans ce cas, vous suivez habituellement le processus suivant:

  • CREATE un TEMPORARY table

  • COPY ou vrac-insérer les nouvelles données dans la table de température

  • LOCK la table cible IN EXCLUSIVE MODE . Cela permet d'autres transactions à SELECT , mais n'apporte aucun changement au tableau.

  • faire un UPDATE ... FROM des enregistrements existants en utilisant les valeurs dans la table de température;

  • faites une INSERT de lignes qui n'existent pas déjà dans le tableau cible;

  • COMMIT , déverrouiller la serrure.

Par exemple, pour l'exemple donné dans la question, à l'aide de multi-évalué INSERT pour remplir la table de température:

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;

Liées à la lecture de

et MERGE ?

SQL-standard MERGE a en fait une sémantique de concurrence mal définie et n'est pas adapté pour upserting sans verrouiller une table d'abord.

c'est une déclaration OLAP vraiment utile pour la fusion de données, mais ce n'est pas vraiment une solution utile pour la concurrence-upsert Sécuritaire. Il ya beaucoup de conseils pour les gens qui utilisent D'autres DBMSes d'utiliser MERGE pour upserts, mais il est en fait mal.

Autres SRD:

335
répondu Craig Ringer 2017-05-31 14:28:18

j'essaie de contribuer avec une autre solution pour le problème d'insertion simple avec les versions pre-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, à 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 $$;

noter que cette solution ne peut être appliquée que s'il n'y a pas de suppression des lignes du tableau .

Je ne sais pas sur l'efficacité de cette solution, mais il me semble assez raisonnable.

25
répondu Renzo 2016-08-22 08:11:02

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

  • insérer, sur conflit - ne rien faire .

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

  • inscrire, sur conflit - faire la mise à jour , préciser la cible de 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 de mise à jour , précisent les conflits de la cible par l'intermédiaire de 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;

4
répondu Eric Wang 2018-01-30 11:22:14
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

3
répondu aristar 2017-03-02 05:25:13

SQLAlchemy upsert for Postgres >=9.5

puisque le Grand post ci-dessus couvre de nombreuses approches SQL différentes pour les versions Postgres (non seulement non-9.5 comme dans la question), je voudrais ajouter comment le faire dans SQLAlchemy si vous utilisez Postgres 9.5. Au lieu de mettre en œuvre votre propre upsert, vous pouvez également utiliser les fonctions de SQLAlchemy (qui ont été ajoutées dans SQLAlchemy 1.1). Personnellement, je recommande l'utilisation de ceux-ci, si possible. Non seulement en raison de la commodité, mais aussi parce qu'il permet à PostgreSQL de gérer toutes les conditions de course qui pourraient se produire.

Cross-posting à partir d'une autre réponse que j'ai donné hier ( https://stackoverflow.com/a/44395983/2156909 )

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

la 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

2
répondu P.R. 2017-06-07 09:38:49

depuis cette question était fermée, je poste ici pour savoir comment vous le faites en utilisant SQLAlchemy. Par récursion, il réessaie un encart en vrac ou une mise à jour pour combattre conditions de course et des erreurs de validation.

D'abord les importations

import itertools as it

from functools import partial
from operator import itemgetter

from sqlalchemy.exc import IntegrityError
from app import session
from models import Posts

Maintenant quelques fonctions d'assistance

def chunk(content, chunksize=None):
    """Groups data into chunks each with (at most) `chunksize` items.
    https://stackoverflow.com/a/22919323/408556
    """
    if chunksize:
        i = iter(content)
        generator = (list(it.islice(i, chunksize)) for _ in it.count())
    else:
        generator = iter([content])

    return it.takewhile(bool, generator)


def gen_resources(records):
    """Yields a dictionary if the record's id already exists, a row object 
    otherwise.
    """
    ids = {item[0] for item in session.query(Posts.id)}

    for record in records:
        is_row = hasattr(record, 'to_dict')

        if is_row and record.id in ids:
            # It's a row but the id already exists, so we need to convert it 
            # to a dict that updates the existing record. Since it is duplicate,
            # also yield True
            yield record.to_dict(), True
        elif is_row:
            # It's a row and the id doesn't exist, so no conversion needed. 
            # Since it's not a duplicate, also yield False
            yield record, False
        elif record['id'] in ids:
            # It's a dict and the id already exists, so no conversion needed. 
            # Since it is duplicate, also yield True
            yield record, True
        else:
            # It's a dict and the id doesn't exist, so we need to convert it. 
            # Since it's not a duplicate, also yield False
            yield Posts(**record), False

et enfin la fonction upsert

def upsert(data, chunksize=None):
    for records in chunk(data, chunksize):
        resources = gen_resources(records)
        sorted_resources = sorted(resources, key=itemgetter(1))

        for dupe, group in it.groupby(sorted_resources, itemgetter(1)):
            items = [g[0] for g in group]

            if dupe:
                _upsert = partial(session.bulk_update_mappings, Posts)
            else:
                _upsert = session.add_all

            try:
                _upsert(items)
                session.commit()
            except IntegrityError:
                # A record was added or deleted after we checked, so retry
                # 
                # modify accordingly by adding additional exceptions, e.g.,
                # except (IntegrityError, ValidationError, ValueError)
                db.session.rollback()
                upsert(items)
            except Exception as e:
                # Some other error occurred so reduce chunksize to isolate the 
                # offending row(s)
                db.session.rollback()
                num_items = len(items)

                if num_items > 1:
                    upsert(items, num_items // 2)
                else:
                    print('Error adding record {}'.format(items[0]))

Voici comment vous l'utilisez

>>> data = [
...     {'id': 1, 'text': 'updated post1'}, 
...     {'id': 5, 'text': 'updated post5'}, 
...     {'id': 1000, 'text': 'new post1000'}]
... 
>>> upsert(data)

l'avantage qu'il présente par rapport à bulk_save_objects est qu'il peut gérer les relations, le contrôle des erreurs, etc sur insert (contrairement à opérations en vrac ).

0
répondu reubano 2017-05-23 12:02:50