restricción - ¿Cómo UPSERT(MERGE, INSERT... ON DUPLICATE UPDATE) en PostgreSQL?

no hay restricción única o de exclusión que coincida con la especificación on conflict

Question

Una pregunta muy frecuente aquí es cómo hacer una inserción, que es lo que MySQL llama INSERT ... ON DUPLICATE UPDATE y el estándar es compatible como parte de la operación MERGE .

Dado que PostgreSQL no lo admite directamente (antes de la página 9.5), ¿cómo hace esto? Considera lo siguiente:

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

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

Ahora imagine que quiere "realzar" las tuplas (2, ''Joe'') , (3, ''Alan'') , por lo que los nuevos contenidos de la tabla serían:

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

De eso es de lo que se habla cuando se habla de una upsert . Fundamentalmente, cualquier enfoque debe ser seguro en presencia de múltiples transacciones que trabajan en la misma tabla , ya sea utilizando un bloqueo explícito o defendiéndose de otra manera contra las condiciones de carrera resultantes.

Este tema se discute ampliamente en Insertar, ¿en una actualización duplicada en PostgreSQL? , pero se trata de alternativas a la sintaxis de MySQL, y ha crecido un poco de detalle no relacionado en el tiempo. Estoy trabajando en respuestas definitivas.

Estas técnicas también son útiles para "insertar si no existe, de lo contrario no hacer nada", es decir, "insertar ... en clave duplicada ignorar".

Answer 1

9.5 y más reciente:

PostgreSQL 9.5 y posteriores admiten INSERT ... ON CONFLICT UPDATE (Y ON CONFLICT DO NOTHING ), es decir, upsert.

Comparación con ON DUPLICATE KEY UPDATE .

Explicación rápida .

Para uso, consulte el manual , específicamente la cláusula conflict_action en el diagrama de sintaxis y el texto explicativo .

A diferencia de las soluciones para 9.4 y versiones anteriores que se ofrecen a continuación, esta función funciona con varias filas en conflicto y no requiere bloqueo exclusivo ni un ciclo de reintento.

El compromiso de agregar la característica está aquí y la discusión sobre su desarrollo está aquí .

Si tiene la versión 9.5 y no necesita ser compatible con versiones anteriores, puede dejar de leer ahora .

9.4 y mayores:

PostgreSQL no tiene ninguna función UPSERT (o MERGE ) UPSERT , y hacerlo de manera eficiente frente al uso concurrente es muy difícil.

Este artículo discute el problema en detalle útil .

En general debes elegir entre dos opciones:

• Operaciones individuales de inserción / actualización en un bucle de reintento; o
• Bloqueando la tabla y haciendo la fusión por lotes

Bucle de reintento de fila individual

El uso de cambios de fila individuales en un ciclo de reintento es la opción razonable si desea que muchas conexiones intenten realizar inserciones simultáneamente.

La documentación de PostgreSQL contiene un procedimiento útil que le permitirá hacer esto en un bucle dentro de la base de datos . Protege contra actualizaciones perdidas e inserta carreras, a diferencia de las soluciones más ingenuas. Sin embargo, solo funcionará en el modo READ COMMITTED y solo es seguro si es lo único que hace en la transacción. La función no funcionará correctamente si los disparadores o las claves únicas secundarias causan violaciones únicas.

Esta estrategia es muy ineficiente. Siempre que sea práctico, debe hacer una cola del trabajo y hacer un aumento de volumen como se describe a continuación.

Muchos intentos de soluciones a este problema no tienen en cuenta las reversiones, por lo que resultan en actualizaciones incompletas. Dos transacciones compiten entre sí; uno de ellos con éxito INSERT s; el otro recibe un error de clave duplicada y realiza una UPDATE lugar. Los bloques de UPDATE esperan a que INSERT deshaga o se confirme. Cuando se revierte, la nueva condición de UPDATE vuelve a verificar que coincida con cero filas, por lo que aunque la UPDATE confirme, en realidad no ha realizado la subida que esperaba. Debe verificar los recuentos de las filas de resultados y volver a intentarlo cuando sea necesario.

Algunos intentos de solución también fallan en considerar las razas SELECT. Si intentas lo obvio y lo 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;

luego, cuando se ejecutan dos a la vez, hay varios modos de falla. Uno es el problema ya discutido con una nueva revisión de la actualización. Otra es donde ambos UPDATE al mismo tiempo, haciendo coincidir cero filas y continuando. Luego, ambos hacen la prueba EXISTS , que ocurre antes del INSERT . Ambos obtienen cero filas, entonces ambos hacen el INSERT . Uno falla con un error de clave duplicada.

Es por eso que necesita un bucle de reintento. Podría pensar que puede evitar errores de clave duplicados o actualizaciones perdidas con SQL inteligente, pero no puede. Debe verificar los recuentos de filas o manejar errores de clave duplicados (según el enfoque elegido) y volver a intentarlo.

Por favor, no ruede su propia solución para esto. Al igual que con la cola de mensajes, es probable que esté mal.

Bulk upert con bloqueo

A veces desea hacer una inserción masiva, donde tiene un nuevo conjunto de datos que desea fusionar en un conjunto de datos existente más antiguo. Esto es mucho más eficiente que los cambios de fila individuales y debería preferirse siempre que sea práctico.

En este caso, normalmente sigues el siguiente proceso:

• CREATE una tabla TEMPORARY

• COPY o insertar de forma masiva los nuevos datos en la tabla temporal

• LOCK la tabla de destino IN EXCLUSIVE MODE . Esto permite otras transacciones para SELECT , pero no hacer ningún cambio en la tabla.

• Realice una UPDATE ... FROM registros existentes usando los valores en la tabla temporal;

• Haga un INSERT de filas que no existen en la tabla de destino;

• COMMIT , liberando la cerradura.

Por ejemplo, para el ejemplo dado en la pregunta, usar INSERT valores múltiples para rellenar la tabla temporal:

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;

Lectura relacionada

• Página wiki de UPSERT
• UPSERTismos en Postgres
• Insertar, en la actualización duplicada en PostgreSQL?
• http://petereisentraut.blogspot.com/2010/05/merge-syntax.html
• Subastar con una transacción
• ¿Es SELECT o INSERT en una función propensa a las condiciones de carrera?
• SQL MERGE en el wiki de PostgreSQL
• La forma más idiomática de implementar UPSERT en Postgresql hoy en día

¿Qué pasa con MERGE ?

El estándar SQL MERGE realidad tiene una semántica de concurrencia mal definida y no es adecuado para realizar cambios sin bloquear primero una tabla.

Es una declaración OLAP realmente útil para la fusión de datos, pero en realidad no es una solución útil para la mejora de la concurrencia segura. Hay muchos consejos para las personas que usan otros DBMS para usar MERGE para actualizaciones, pero en realidad está mal.

Otros DBs:

• INSERT ... ON DUPLICATE KEY UPDATE en MySQL
• MERGE desde MS SQL Server (pero vea más arriba sobre problemas de MERGE )
• MERGE de Oracle (pero vea más arriba sobre los problemas de MERGE )

Answer 2

SQLAlchemy mejora para Postgres> = 9.5

Ya que la publicación grande anterior cubre muchos enfoques SQL diferentes para las versiones de Postgres (no solo no 9.5 como en la pregunta), me gustaría agregar cómo hacerlo en SQLAlchemy si está utilizando Postgres 9.5. En lugar de implementar su propio incremento, también puede usar las funciones de SQLAlchemy (que se agregaron en SQLAlchemy 1.1). Personalmente, recomendaría usar estos, si es posible. No solo por conveniencia, sino también porque permite que PostgreSQL maneje cualquier condición de carrera que pueda ocurrir.

Publicación cruzada de otra respuesta que di ayer ( https://.com/a/44395983/2156909 )

SQLAlchemy admite ON CONFLICT ahora con dos métodos on_conflict_do_update() y on_conflict_do_nothing() :

Copiando de la documentación:

from sqlalchemy.dialects.postgresql import insert

stmt = insert(my_table).values(user_email=''[email protected]'', data=''inserted data'')
stmt = stmt.on_conflict_do_update(
    index_elements=[my_table.c.user_email],
    index_where=my_table.c.user_email.like(''[e-mail]''),
    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 3

Aquí hay algunos ejemplos para insert ... on conflict ... ( pág. 9.5+ ):

• Insertar, en conflicto - no hacer nada .
  insert into dummy(id, name, size) values(1, ''new_name'', 3) on conflict do nothing;

• Insertar, en conflicto - actualizar , especifique el destino del conflicto a través de la columna .
  insert into dummy(id, name, size) values(1, ''new_name'', 3) on conflict(id) do update set name = ''new_name'', size = 3;

• Insertar, en conflicto - actualizar , especifique el destino del conflicto mediante el nombre de la restricción .
  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 4

Como esta pregunta se cerró, estoy publicando aquí para ver cómo lo hace usando SQLAlchemy. A través de la recursión, reintenta una inserción o actualización masiva para combatir las condiciones de la carrera y los errores de validación.

Primero las importaciones

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

Ahora un par de funciones auxiliares.

def chunk(content, chunksize=None):
    """Groups data into chunks each with (at most) `chunksize` items.
    https://.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

Y finalmente la función de subida.

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]))

Así es como lo usas.

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

La ventaja que esto tiene sobre bulk_save_objects es que puede manejar relaciones, verificación de errores, etc. en la inserción (a diferencia de las operaciones masivas ).

Answer 5

Estoy tratando de contribuir con otra solución para el problema de inserción única con las versiones pre-9.5 de PostgreSQL. La idea es simplemente intentar realizar primero la inserción, y en caso de que el registro ya esté presente, actualizarlo:

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

Tenga en cuenta que esta solución solo se puede aplicar si no hay eliminaciones de filas de la tabla .

No conozco la eficacia de esta solución, pero me parece bastante razonable.

Answer 6

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

Probado en Postgresql 9.3